Power query, поиск проблемного запроса/ошибки и расчет времени работы запроса, Поиск ошибок в сложных файлах,состоящих из множества запросов, и замер скорости работы запросов
мной был сформирован файл-консолидатор информации из множества других отчётов в power query. Эти отчёты готовят коллеги, структура не меняется, но всегда может появиться лишний столбец или ещё что-нибудь, что ломает итоговый запрос консолидатора. Итоговый запрос работает просто, объединяет все запросы в книге. Если один запрос некорректный, то итоговый запрос не загружается и найти прохвоста вручную ах как не просто (запросов очень много). Есть ли какая-то функция или ещё что-то, что поможет найти сбойный запрос и причину возникшей ошибки (каждый раз может быть разной). Также интересует узнать, сколько отрабатывает каждый запрос, как это можно сделать?
p.s. обсуждаем не проблему файла, а метод/подход, приложить файл нет возможности и надобности.
Якубович, что можно посоветовать, учитывая что с вашей точки зрения
Цитата
ArNikiTenS написал: обсуждаем не проблему файла, а метод/подход
могу дать совет в стиле Тони Роббинса:
Это значит что делать запросы нужно так, чтобы либо объезжать возможные ошибки, либо чтобы их поиск был максимально упрощен. Для этого можно использовать конструкцию try ... otherwise ..., например.
Murderface_ написал: Можно просто задать требуемое имя для столбцов в итоговом запросе. Таким образом, если появится лишний столбец, то вы его не увидите.
Возможно не правильно сформулировал, предположим есть 20 запросов, они преобразуют файлы от 20 человек до 2 столбцов: Дата и Сумма. Так как заполняют люди, то всегда может всплыть косяк заполнения данных (например 30.02.2019.). Так как запросы сохраняются в модель данных, то выводится только итоговый запрос, который сконсолидировал данные всех 20 человек. Если хотя бы 1 запрос содержит некорректные данные, то весь запрос не исполнится и не загружается в таблицу.
Вопрос в том, что надо найти тот самый запрос, который не отработал. Есть какая-то фишка в PQ для быстрого поиска засбоивших запросов?
Так если их не 20,а 100, по 1000 строк минимум, глазами не найти. У PQ же определенно есть логи исполнения запросов, в настройках есть некая "трассировка", может это она?
В приложенном файле 3 запроса, не понятно кто нашкодил (3 запрос), итоговая таблица выдает ошибку.
Внимание, вопрос. Как не руками и не нажимая на каждый запрос определить, кто выдаёт ошибку.
Также интересует, как узнать скорость обработки запроса. Хочется мне понять, кто среди запросов самый медленный, как это можно сделать?
P.s. по правилам форума нельзя выкладывать файлы более 100 кб, тогда как выкладывать "тяжёлые файлы"? P.s.s. файл сформирован в 19 офисе, здесь выдается кол-во ошибок, на работе стоит 16 офис и в файле консолидаторе число ошибок не выдается.
let
Источник = {Таблица1, Таблица2, Таблица3},
Индекс = List.Transform(Источник, each Table.AddIndexColumn(_, "Строка", 1, 1)),
Строка = List.Transform(Индекс, each Table.TransformColumnTypes(_,{{"Строка", type text}})),
SelectErrors = List.Transform(Строка, Table.SelectRowsWithErrors),
toTable = Table.FromList(SelectErrors, Splitter.SplitByNothing()),
Add = Table.AddColumn(toTable, "Количество", each Table.RowCount([Column1])),
Add2 = Table.AddColumn(Add, "Строки", each Text.Combine([Column1][Строка], ", "))
in
Add2
Наверно, ещё можно названия таблиц прилепить, если они называются чуть хитрее, чем Таблица1, Таблица2, Таблица3...
let
Источник = {Таблица1, Таблица2, Таблица3},
SelectErrors = List.Transform(Источник, each Table.SelectRowsWithErrors(Table.AddIndexColumn(_,"id"))),
toTable = Table.AddIndexColumn(Table.FromList(SelectErrors, Splitter.SplitByNothing()),"Id")
in List.RemoveNulls(Table.AddColumn(toTable,"a", each [a="Таблица "&Text.From([Id]),b=Table.RowCount([Column1]),
d=Text.Combine(List.Transform([Column1][id],Text.From),", "),c=if b=0 then null else a&" "&d][c])[a])
Считаю второй вариант Михаила наиболее точно решает поставленную задачу (В варианте Андрея еще не разобрался).
Осталось придумать, как запихнуть автоматизацию выборов запросов через #shared и получится великолепное и изящное решение.
Также висит вопрос по скорости отработки запросов, как нам искать улиток из семейства запросов? Возможно ли как-то рассчитать скорость не только всего запроса, но и его операторов в отдельности?
Якубович написал: как запихнуть автоматизацию выборов запросов через #sourse и получится великолепное и изящное решение.
наверное вы имели ввиду #shared. Смотря где вы планируете смотреть результат. Если только в редакторе запросов - то отобрать только таблицы из всего большого перечня проверить их на ошибки можно так:
Код
// tester
let
Источник = Record.ToTable(#shared),
FilterSelfOut = Table.SelectRows(Источник, each [Name] <> "tester"),
FilterTables = Table.SelectRows(FilterSelfOut, each Type.Is(Value.Type([Value]), type table)),
RowsWithErrors = Table.AddColumn(FilterTables, "Errors", let guid = Text.NewGuid() in each let errs = Table.AddIndexColumn(Table.SelectRowsWithErrors([Value]), guid) in if Table.IsEmpty(errs) then null else errs, type table),
FilterEmpty = Table.SelectRows(RowsWithErrors, each ([Errors] <> null)),
Removed = Table.RemoveColumns(FilterEmpty,{"Value"})
in
Removed
запрос называется tester, и в 4 строчке (2-й шаг) мы удаляем его из списка во избежание циркулярных ссылок. Но, повторю, в таком виде - без явного перечисления названий запросов - будет работать только внутри редактора запросов Power Query
Якубович, время отработки можно замерить только у того запроса, который выводит информацию куда-то (в таблицу/ модель/ кэш сводной) при этом не получится вычленить скорость выполнения его составляющих (тех запросов, на которые он ссылается), т.к. интерпретатор не рассматривает ваши запросы как отдельные, для него это все - один большой запрос, результат которого куда-то выводится.