Сергей У, power query при подключении к файлу /папке с файлами выводит таблицу со всеми объектами в файле/файлах, в т.ч. имена листов с указанием типа "sheet". Могут потребоваться лишь минимальные действия, чтобы вытащить имя листа, в зависимости от того, как производится подключение к файлам. Выгружайте потом имя листа в любую ячейку.

Irina.pohvalit, не понятно, как выглядит файл, который будет обрабатываться и как он изменяется, но напр. можно макрос записать в рекордере на тот же "Текст по столбцам". Но там уже скинули макрос, не знаю как и что он делает.

Irina.pohvalit, Данные - Текст по столбцам чем не устраивает?

если через промежут. шаги, то добавить соседний столбец с условием =ЕСЛИ(ПСТР([@Исполнитель];1;1)="С";[@Ключ];"")
затем под столбцом ввести как формулу массива , заменив все диапазоны ячеек на диапазон строк добавленного столбца (взял эту формулу с этого форума):
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A4:A53)>0;ПОИСКПОЗ(A4:A53;A4:A53;0);"");ЕСЛИ(ДЛСТР(A4:A53)>0;ПОИСКПОЗ(A4:A53;A4:A53;0);""))>0;1))

surkenny, спасибо! обязательно изучу

surkenny, не очень понятно про имена шагов, но оч интересно, если правильно вас понял - такого не встречал - скиньте плиз пример

Р.Н. С.,

№ встречи в первом пользовательском столбце:
    = #"Измененный тип"[Column2]{1}       // обращение ко 2ой строке в столбце Column2 - значение 6 протянется на все строки польз-го столбца

длительность во втором польз. столбце (в минутах):
= Duration.TotalMinutes(
функция перевода в формат "Дата +время" (#"Измененный тип"[Column2]{4}) -
функция перевода в формат "Дата +время"( #"Измененный тип"[Column2]{3} )
)

  // также как выше указано - обращаемся к соответствующим строкам в столбце Column2 и вытягиваем конец и начало встречи соответственно, а функция Duration.TotalMinutes переводит в минуты разницу во времени - но сначала вам надо перевести каждое значение времени в формат "дата +время" вручную - вы не сможете применить формат "дата + время" ко всему столбцу Column2 т.к. у вас в этом столбце данные разных форматов и возникнут ошибки, соответственно - сами посмотрите какую функцию применить и замените текст "функция перевода в формат "Дата +время"" на эту функцию.
Если вы не понимаете о чем речь, то надо посмотреть мануалы для начинающих.

AlexDen, у меня опыт общения с PQ 2 года с нуля, а ежедневного общения, учитывая просмотры мануалов, месяцев 6))

я не оч. въезжал в ваши true\false, исходя из того, что если получен признак, что в строке есть ошибки, то поставить значение true\false если это вам необходимо, уже никакого труда не составит.

Принцип моего решения:
1. формула пишется в пользовательском столбце

2. каждую строку превращаем в список значений через Record.ToList
при этом, чтобы не возникло циклической ссылки, обращаемся к этой же таблице, но в предыдущем шаге (иначе пользовательский столбец также закинет в список и будет цикл. ссылка) - если вам это не понятно, то погуглите "обращение к предыдущему шагу в power query" или т.п., но учитывая ваши скрипты, думаю это вам д.быть понятно. Кратко - текущая таблица остается той же самой, что и на предыдущем шаге, только со столбцом индекса, и обращаясь к таблице в предыдущем шаге по № строки = № индекса, вы получаете по составу ту же самую строку, что и в текущей таблице.

3. по списку делаем поиск значений через List.Contains
функцию подобрал наугад - она при поиске если натыкается на значение error, то выдает результат как error, если бы не реагировала на ошибку то была бы др. функция, не имеет значения какая, по аналогии с value.metadata - главное, чтобы натыкаясь на ошибку выдавало ошибку как результат
как искомый текст использовал "собаки" чтобы точно такого текста не нашло во всей таблице и в результирующем столбце было только false и 1.

4. строки, содержащие ошибку в любом из столбцов (т.к. мы делаем поиск по списку всех значений из строки = значениям из каждого столбца строки), будут иметь признак 1.
"почему-то (это не с точки зрения критики, это про мое непонимание) работает. где не Error, дает false, где Error, там "1" -  да, так и задумано, false - это результат работы List.Contains, а 1 - это где  List.Contains сломалась встретив значение erorr и уже otherwise выдало 1.

выложу файл с решением позже, т.к. в файлах с рабочего компа сохраняется дофига метаданных (п.с. пора бы уже админам придумать что-то, чтобы удалялись метаданные автоматом, а то палево палево, иначе не скажешь)

AlexDen, я поправил свой последний пост в части производительности - у меня работает мгновенно на 2,5 млн строк х 136 столбцов на среднем ноуте

Вариант решения работающий для всех столбцов:
добавляем столбец индекса с 0, обращаемся к шагу до добавления индекса (ниже шаг с именем qqq) и вместо номера строки подставляем индекс, превращаем каждую строку в список значений (т.е. из каждого столбца), и ищем по списку какое-нибудь нереальное значение - на строках с ошибками функция List.Contains вернет ошибку, а otherwise вернет 1

можно поподбирать различные функции List. - у меня на одной таблице с ошибками отлично сработал List.Average, но на другой, в которой нет ошибок, все равно отрабатывает все строки как имеющие ошибку

try List.Contains(Record.ToList(qqq{[Индекс]}), "@@@@@@@@") otherwise 1

на 2,5 млн строк х 136 столбцов с огромными текстовыми данными в половине ячеек работает мгновенно на i7 16gb ram

 del

AlexDen, "сделать на каждый столбец доп столбец ошибка/неошибка" это совсем мягко говоря не годится)) т.к. с неск. даже десятками столбцов, не говоря про сотни, это по затратам времени нереально,  но если хочется , то через try..otherwise и if/then/else это можно сделать в одном пользовательском столбце - проверил, рабочий способ)

Чтобы не проверять все столбцы, я попытался через List. Generate сделать с накоплением кол-ва ошибок в параметре до первой ошибки после которой цикл завершается. Но не получилось сходу, т.к. оказалось, что функция Value.Metadata никаким образом не хочет работать с подставными именами столбцов в любом виде, т.е. она обязательно должна ссылаться на "живой" столбец в самой таблице, в которой этот столбец находится, по кр. мере я не смог ей скормить никаким образом столбцы через параметры. Буду пытаться просто через try реализовать.

Но повторюсь, я легко и быстро реализовал проверку на ошибки всех столбцов через группировку таблицы - см. выше написал как, если не понятно - прикреплю файл, но не проверял на большой таблице, проверю отпишусь, но 99%, что на огромной таблице будет тормозить. Способ полностью рабочий. Но у меня уже просто спортивный интерес сделать это через List.Generate)) и, думается, это единственный способ исключить проверку всех столбцов.

AlexDen, я так понимаю, что это только для одного столбца проверка? а если столбцов 100-200-....  и в каком-то из них только ошибка?

AlexDen, я реализовал через добавление столбца индекса -> группировку строк по столбцу индекса по двум уровням группировки "Кол-во уникальных строк" и "Все строки" -> использования try Value.Metadata() otherwise 1 к промежуточному результату - > разворачивания обратно таблицы   - так учитываются значения во всех столбцах с ошибками на условных данных, но способ слишком костыльный для миллионов строк - будет тормозить
подождем ответа от коллег как прогнать циклом
п.с.: сдается мне что Value.Metadata() будет сильно тормозить сама по себе на миллионах строк, т.к. выдает результат record - слишком "грузный" результат

Vladimir K, не вник, но возможно это поможет: https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=136449&TITLE_SEO=136449-poisk-maksimalnoy-daty-dlya-odinakovykh-znacheniy

там смысл в виртуальной группировке "налету" дат по каждому значению признака и налету же выбора макс или мин даты по группе

коллеги, помогите мне загнать в "try Value.Metadata() otherwise 1" список имен столбцов (пусть он называется spisok) или как-то через each т.е чтобы выражение применилось ко всем столбцам строки, не могу справиться

это уже смотря какая у вас задача
мне лично надо заранее отследить, какие данные выдают ошибку и почему и сразу что-то сделать чтобы ее избежать до добавления в модель данных, т.к. загрузка в модель данных может занимать часы, а то и сутки в некоторых случаях

ее надо использовать с try otherwise и в качестве значения otherwise написать 1 или любое другое - см. выше - проверил - работает для всех типов данных -  на error выдает заданное значение, на остальные данные выдает запись
на одном столбце работает, но как загнать в нее список столбцов - надо подумать, возможно через функцию цикла какую-нибудь, пусть коллеги подставят перебор столбцов в цикле

AlexDen, кажется, подобрал функцию - Value.Metadata
попробуйте список столбцов в нее загнать ну или условия если-тогда-если-....
или подобрать др. функцию

AlexDen, проверил на условных данных: Type.IsNullable создает ошибки не только для error, но и для др. типов данных, надо другую подобрать

мне самому интересно стало, т.к. постоянно на млн-ах строк сталкиваешься с ошибками, и вывод отдельного запроса с ошибками через добавление в модель данных занимает оч. много времени, поэтому легче сразу же в пользовательском столбце отслеживать наличие ошибок до добавления в модель.

AlexDen, можно применить для каждой строки и для каждого столбца в ней try/ otherwise вместе с какой-то функцией, которая сама по себе не сгенерирует ошибку для любого типа значения, ну т.е. например какую-то функцию определения типа данных (есть такая??) или т.п. Естественно, любая функция для значения error сгенерирует error.
Т.е. в пользовательском столбце пишем:
try какаято функция ([столбец/список столбцов]) otherwise "1"
1 будет признаком строки с ошибками

upd: например, применить функцию Type.IsNullable или любую подобную возвращающую тру/фолс для любого значения

teplovoz, добавьте столбец индекса, потом столбец, в котором прописать условие: если [столбец такой-то]= N° то = индекс     так вы вытащите номер строки, с которой начинается таблица (в которой находится заголовок). Далее подставить номер строки с заголовком в функцию удаления верхних строк или т.п. что вам нужно.

AlexDen, вообще цель сделать столбец с признаком для строк с ошибкой в любом столбце? можно добавить результат в модель данных, и тогда в списке запросов под именем запроса отобразиться кол-во ошибок, щелкнуть на них как обычно и появится запрос со строками с ошибками, и как-то вытащить номера этих строк, если есть такая функция,, потом смэчить с основным запросом.
upd: предварительно сделать столбец индекса в основном запросе, тогда в запросе с ошибками будет столбец с индексами этих строк
через одно место, но вариант

какие форматы файлов?
power query может подключиться к папке и в pdf- и excel- файлах кол-во листов  сосчитает, и имена, конечно, выведет, в файлах остальных форматов - надо смотреть, я с файлами word в docx-формате не справился
путь к папке можно будет указать в ячейке, power query будет брать путь из ячейки

можно сделать через добавление своего индекса для каждой группы строк по сотруднику

в запросе result (2) делаете группировку по столбцу Сотрудник с параметром "Все" - все сворачивается в таблицу с вложенными таблицами - каждая вложенная таблица это таблица со строками только по одному сотруднику - ну т.е. ваша изначальная таблица разделилась на несколько таблиц  по ФИО
добавляете пользовательский столбец и в нем пишете формулу добавления индекса
Table.AddIndexColumn([Количество], "Индекс", 0, 1, Int64.Type)
п.с.: [Количество] - это столбец с вложенными таблицами, который появился после группировки
далее появится пользовательский столбец с вложенными таблицами в каждой из которых появился столбец индекса начиная с 0
далее разворачиваете новый столбец с вложенными таблицами щелчком по стрелкам в заголовке столбца
теперь у вас каждая группа строк по одному сотруднику начинается с индекса = 0, т.е. это признак, по которому можно определить первую строку в каждой группе
а далее дело техники - добавляете пользовательский столбец и прописываете нужные условия: else [Индекс] = 0 then ..............
не стал дописывать до конца - справитесь (все строки = 0  заменить на нужное значение из соседних столбцов, а все строки <>0 заменить на 0)
далее код для запроса result (2):

let
 data = Excel.CurrentWorkbook(){[ Name = "DATA2" ]}[Content],
   #"Сгруппированные строки" = Table.Group(data, {"Сотрудник"}, {{"Количество", each _, type table [Source.Name=text, Шифр проекта=text, Сотрудник=text, Роль в проекте=text, Дисциплина=text, Локация=text, Подразделение=text, Должность=text, Статус=text, Трудозатраты=number]}}),
   #"Добавлен пользовательский объект" = Table.AddColumn(#"Сгруппированные строки", "Пользовательский", each Table.AddIndexColumn([Количество], "Индекс", 0, 1, Int64.Type)),
   #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"Количество"}),
   #"Развернутый элемент Пользовательский" = Table.ExpandTableColumn(#"Удаленные столбцы", "Пользовательский", {"Source.Name", "Шифр проекта", "Сотрудник", "Роль в проекте", "Дисциплина", "Локация", "Подразделение", "Должность", "Статус", "Трудозатраты", "Индекс"}, {"Source.Name", "Шифр проекта", "Сотрудник.1", "Роль в проекте", "Дисциплина", "Локация", "Подразделение", "Должность", "Статус", "Трудозатраты", "Индекс"})
in
   #"Развернутый элемент Пользовательский"

nck, может быть укрупненные группы сделать? но в любом случае необходимо исходить лишь из целей, зачем вам это вообще нужно и условий договора как принимаются работы - как там объемы считаются? может быть вообще за полугодие.
п.с.: я аудитором работаю, я бы договор посмотрел -если это нужно для оплаты, то в любом случае в договоре все должно быть оговорено как считается в каких случаях. это имхо конечно.

nck, если вы сами точно не знаете, как правильно пропорционально разделить по пересечениям - напишите хотя бы кратко физический/экономический или др. смысл зачем это распределяется.

nck, как делить на группы, можете знать и объяснить только вы. Соотв-но, что делать, если пересекаются - также только вы можете пояснить.

nck, такого условия нет в задаче, тут думается мало топографов
разделить значения пикетов в обеих таблицах через + на 2 части и сделать проверку условия на каждую часть как надо, по-топографически  - по выполнению условия сделать признак группы

nck, у вас в формуле расчета почему-то не включены ячейки с37:с42 хотя в них также 114 значение - какая логика?
а так взять начало и конец каждого пикета и прописать условие если >=нач.знач и <=кон. знач. то признак группы присвоить при выполнении в обеих таблицах и дальше их женить/группировать