Пользовательские функции запросов LuckyTemplates

В сегодняшнем руководстве мы создадим настраиваемую функцию запроса LuckyTemplates, которая будет работать с распространенным сложным в обработке форматом сложенных данных. Этот метод легко повторно использовать в нескольких отчетах и ​​нескольких таблицах. Вы можете посмотреть полное видео этого урока в нижней части этого блога.

Это продолжение урока, который я недавно сделал, где мы имели дело с распространенным сложным для обработки форматом сложенных данных и использовали Modulo, Pivot и некоторые функции очистки с Power Query для преобразования этих данных в организованный и функциональный формат.

Весьма вероятно, что вам придется повторить этот процесс. Если этот тип формата возник в результате копирования и вставки, это, вероятно, произойдет снова. Итак, в этом случае вы, вероятно, захотите создать пользовательскую функцию, которую можно будет повторно использовать в нескольких отчетах и ​​нескольких таблицах, независимо от того, как они названы и что еще находится в этих таблицах.

Оглавление

• Как создавать и отлаживать пользовательские функции запросов LuckyTemplates
• Заключение

Как создавать и отлаживать пользовательские функции запросов LuckyTemplates

Давайте перейдем к Power Query. Если мы перейдем к нашей таблице данных в расширенном редакторе, у нас будет M-код того, как мы в конечном итоге перешли от данных стека к желаемому формату.

Если мы хотим использовать это повторно, мы просто щелкаем правой кнопкой мыши на Data и говорим «Создать функцию» .

Он говорит, что не будет ссылаться ни на один параметр, но можно создать функцию без параметров. Мы собираемся ответить «Да», потому что мы собираемся добавлять параметры по ходу работы в расширенном редакторе. Мы назовем эту функцию Unstack — 3 Elements только потому, что у нас есть три строки в необработанном формате. Если бы у нас был формат с двумя строками, мы бы, вероятно, построили отдельный формат, состоящий из двух или четырех элементов.

Итак, теперь у нас есть этот Invoke, у нас нет параметров, и давайте перейдем в расширенный редактор и начнем работать над этим.

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

Мы хотим определить параметр, который будет входить. Мы делаем это с помощью открывающей скобки, а затем имени параметра. Давайте назовем это Stack , который будет тем одним столбцом данных, который представляет собой неформатированные данные стека, полученные из вставки адресов электронной почты.

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

Мы назовем его ConvertStack , и он будет использовать функцию с именем Table.FromList . Как и многие М-функции, она делает именно то, что говорит. Он просто принимает список. В этом случае список будет нашим списком стека. И затем нам нужно заменить здесь Source нашим ConvertStack. Это выглядит довольно хорошо! У нас нет синтаксических ошибок, поэтому давайте нажмем «Готово».

Теперь он дает нам то, что мы хотели, а именно возможность выбирать столбец.

Затем мы собираемся выбрать TestData, то есть данные с неправильным форматом. Мы выберем столбец «Значение» и нажмем «ОК». После этого нажимаем Invoke.

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

Вместо того, чтобы запускать это на данный момент как функцию, мы собираемся запустить его как запрос. Мы просто собираемся вручную инициировать этот вызов стека. Мы должны определить, что такое стек, потому что мы не рисуем его посредством взаимодействия, выбирая его в Invoke. Итак, у нас есть Stack, равный TestData, и столбец Value TestData.

Теперь он разбивает его на Прикладные Шаги. Это поможет нам понять, почему это не работает. Обратите внимание на одну интересную вещь, когда мы извлекаем Stack. Первоначально он извлекает TestData[Value], но когда мы преобразуем его в таблицу, заголовок столбца вместо Value становится Column1.

Помните сообщение об ошибке, оно заключалось в том, что не удалось найти столбец «Значение». Причина, по которой не удалось найти столбец Value, заключается в том, что функция Table.FromList переименовывает его в Column1.

Если мы перейдем к Applied Steps, вы увидите, что именно на добавленном пользовательском этапе мы получаем ошибку. Здесь значение записи не было найдено.

Итак, если мы заходим в расширенный редактор, мы находим это поле Custom и видим, что оно находится в Text.Remove, где мы удаляем те ненужные символы, которые нам не нужны, но это все еще относится к поле Значение. Мы изменим это на Column1. Точно так же в поле «Удаленные столбцы» он ссылается на «Значение», поэтому мы также изменим его на «Столбец1».

И когда мы доходим до конца Прикладных шагов, это дает идеальный конечный результат.

Теперь, когда мы знаем, что все работает правильно, осталось сделать еще одну вещь. Помните, что для отладки мы превратили пользовательскую функцию в запрос. Итак, теперь нам нужно взять этот запрос и превратить его обратно в функцию.

Создание пользовательской функции очистки текста в
M-коде Power Query В пользовательских столбцах Power Query | Запрос функции LuckyTemplates
и операторы в редакторе запросов

Заключение

В этом руководстве мы создали эту пользовательскую функцию, отладили ее, протестировали, и она отлично работает. Итак, теперь, когда у нас есть стек из трех элементов, мы можем просто выбрать эту таблицу, выбрать этот столбец в таблице и запустить эту пользовательскую функцию. Затем примените Modulo, unpivot и очистите.

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

Всего наилучшего!