Использование пользовательских функций

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

Вы можете вручную создать настраиваемую функцию Power Query, используя язык формул Power Query M, или пользовательский интерфейс Power Query предлагает вам функции для ускорения, упрощения и улучшения процесса создания и управления настраиваемой функцией.

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

Создание пользовательской функции из кода в пользовательском интерфейсе

1. Используйте интерфейс соединителя для подключения к данным, где размещены ваши данные. Выбрав данные, нажмите кнопку Преобразовать данные или Изменить. Это перенаправит вас в рабочее пространство Power Query.

2. Щелкните правой кнопкой мыши пустое место в области запросов слева.

3. Выберите пустой запрос.

4. В новом окне пустого запроса выберите меню главная, а затем расширенный редактор.

5. Замените шаблон пользовательской функцией. Рассмотрим пример.

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Выберите Готово.

Дополнительные сведения о разработке пользовательских функций с помощью языка формул Power Query M см. в этой статье: Understanding Power Query M Functions. В следующих разделах содержатся руководства, описывающие использование пользовательского интерфейса Power Query для разработки пользовательских функций без написания кода и инструкций по вызову пользовательской функции в запросе.

Создание пользовательской функции из руководства по справочнику по таблицам

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

Начните с использования функции работы с папками, чтобы перейти к папке, в которой находятся ваши файлы, и выберите Трансформировать данные или Редактировать. Эти шаги ведут к рабочей среде Power Query. Щелкните правой кнопкой мыши по двоичному значению на ваш выбор из поля Content и выберите опцию Добавить как новый запрос. В этом примере был выбран первый файл из списка, который может быть файлом апреля 2019.csv.

Этот параметр создает новый запрос с шагом навигации непосредственно к выбранному файлу как к двоичному объекту, при этом имя нового запроса — это путь к выбранному файлу. Переименуйте этот запрос в Пример файла.

Создайте новый параметр с именем Файл Параметр и типом бинарный. Используйте запрос примера файла в качестве значения по умолчанию и текущего значения.

Щелкните правой кнопкой мыши на параметре файла в панели запросов . Выберите параметр Reference.

Переименуйте только что созданный запрос из параметра файла (2) в преобразование примера файла.

Щелкните правой кнопкой мыши этот новый запрос преобразования примера файла и выберите параметр Создать функцию.

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

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

• Все параметры, на которые ссылались в запросе к файлу примера для преобразования.
• Запрос преобразования файла примера, более известный как запрос примера .
• Ваша недавно созданная функция, в данном случае, Преобразовать файл.

Применение преобразований к образцу запроса

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

Первое преобразование, которое должно произойти с этим запросом, — это то, что интерпретирует двоичный файл. Вы можете щелкнуть правой кнопкой мыши на двоичном файле в области предварительного просмотра и выбрать вариант CSV, чтобы преобразовать двоичный файл в CSV-файл.

Формат всех CSV-файлов в папке одинаков. Все они имеют заголовок, охватывающий первые четыре строки. Заголовки столбцов расположены в строке 5, а данные начинаются с строки 6 вниз, как показано на следующем рисунке.

Следующий набор шагов преобразования, которые необходимо применить к примеру файла преобразования:

1. Удалить первые четыре строки. Это действие избавляется от строк, которые считаются частью раздела заголовка файла.

   

   Заметка

   Чтобы узнать больше о том, как удалить строки или отфильтровать таблицу по позиции строки, перейдите к разделу "Фильтрация таблицы по позиции строки".

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

   

После поднятия заголовков столбцов Power Query по умолчанию добавляет новый шаг Changed Type, который автоматически обнаруживает типы данных для каждого столбца. Запрос для образца файла преобразования выглядит так, как на следующем изображении.

Вызов пользовательской функции в качестве нового столбца

После создания пользовательской функции и всех включенных шагов преобразования можно вернуться к исходному запросу, в котором есть список файлов из папки (CSV-файлы в этом примере). На вкладке Добавить столбец в ленте выберите Вызов пользовательской функции из группы Общие. В окне Invoke Custom Function введите Выходная таблица в качестве нового имени столбца. Выберите имя функции, Transform file, в раскрывающемся списке Function query. После выбора функции в раскрывающемся меню отображается параметр функции и можно выбрать столбец из таблицы, используемый в качестве аргумента для этой функции. Выберите столбец содержимого в качестве значения или аргумента, передаваемого для параметра файла .

После выбора ОКсоздается новый столбец с именем Выходная таблица. Этот столбец содержит в ячейках значения таблицы , как показано на следующем рисунке. Для простоты удалите все столбцы из этой таблицы, кроме Name и выходных таблиц.

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

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

Добавление нового параметра в существующую пользовательскую функцию

Представьте, что есть дополнительное требование в дополнение к тому, что вы уже создали. Новое требование требует, чтобы перед объединением файлов отфильтровать данные внутри них, чтобы получить только строки, в которых страна равно Панаме.

Чтобы выполнить это требование, создайте новый параметр с именем Market с текстовым типом данных. Для текущего значениявведите значение Панама.

С помощью этого нового параметра выберите запрос преобразования файла образца и отфильтруйте поле Страна, используя значение из параметра Рынок.

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

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

Чтобы устранить ошибки, дважды щелкните Вызов пользовательской функции в Примененных шагах, чтобы открыть окно Вызова пользовательской функции. В параметре Рынок вручную введите значение Панама.

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

Создание пользовательской функции из повторно используемых фрагментов логики

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

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

Сначала у вас есть параметр, имеющий значение, которое служит примером. В этом случае это значение PTY-CM1090-LAX.

В этом параметре создается новый запрос, в котором применяются необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-LAX на несколько компонентов:

• Источник = PTY
• пункт назначения = LAX
• Авиалиния = CM
• Идентификатор рейса = 1090

Следующий код M демонстрирует этот набор преобразований.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Затем этот запрос можно преобразовать в функцию, щелкнув правой кнопкой мыши запрос и выбрав Создать функцию. Наконец, можно вызвать пользовательскую функцию в любой из запросов или значений, как показано на следующем рисунке.

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