Power query excel преобразование запроса в функцию
Если вы находите себя в ситуации, когда необходимо применить один и тот же набор преобразований к разным запросам или значениям, создайте Power Query настраиваемую функцию, которую можно использовать повторно столько раз, сколько потребуется. Power Query настраиваемая функция — это сопоставление из набора входных значений с одним выходным значением и создается из собственных функций и операторов M.
Хотя вы можете вручную создать собственную Power Query настраиваемую функцию с помощью кода, как показано в описании функций Power Query M, пользовательский интерфейс Power Query предлагает функции для ускорения, упрощения и улучшения процесса создания пользовательской функции и управления ею. В этой статье основное внимание уделяется этому интерфейсу, предоставляемому только через пользовательский интерфейс Power Query и как максимально эффективно использовать его.
В этой статье описывается, как создать пользовательскую функцию с Power Query с помощью общих преобразований, доступных в пользовательском интерфейсе Power Query. В нем рассматриваются основные понятия создания пользовательских функций, а также ссылки на дополнительные статьи в Power Query докуменации для получения дополнительных сведений о конкретных преобразованиях, на которые ссылается эта статья.
Создание пользовательской функции из ссылки на таблицу
Для этого примера можно скачать примеры файлов, используемых в этой статье, по следующей ссылке для скачивания. Для простоты в этой статье будет использоваться соединитель папок. Дополнительные сведения о соединителе папок см. в разделе "Папка". Цель этого примера — создать пользовательскую функцию, которая может быть применена ко всем файлам в этой папке перед объединением всех данных из всех файлов в одну таблицу.
Начните с использования соединителя папок, чтобы перейти к папке, в которой находятся файлы, и выберите "Преобразовать данные " или "Изменить". Это приведет вас к Power Query опыту. Щелкните правой кнопкой мыши выбранное двоичное значение в поле "Содержимое" и выберите пункт "Добавить как новый запрос ". В этом примере вы увидите, что выбор был сделан для первого файла из списка, который будет файлом в апреле 2019.csv.
Этот параметр фактически создаст новый запрос с шагом навигации непосредственно к этому файлу в виде двоичного файла, а имя этого нового запроса будет путь к файлу выбранного файла. Переименуйте этот запрос в пример файла.
Создайте новый параметр с именем "Параметр файла". Используйте запрос примера файла в качестве текущего значения, как показано на следующем рисунке.
Мы рекомендуем ознакомиться со статьей о параметрах, чтобы лучше понять, как создавать параметры и управлять ими в Power Query.
Пользовательские функции можно создавать с помощью любого типа параметров. Для какой-либо пользовательской функции в качестве параметра не требуется иметь двоичный файл.
Тип двоичного параметра отображается только в раскрывающемся меню "Параметры" в раскрывающемся меню "Типпараметров" при наличии запроса, результатом которого является двоичный файл.
Можно создать пользовательскую функцию без параметра. Это часто встречается в сценариях, когда входные данные можно вывести из среды, в которой вызывается функция. Например, функция, которая принимает текущую дату и время среды и создает определенную текстовую строку из этих значений.
Щелкните правой кнопкой мыши параметр файла на панели "Запросы ". Выберите параметр "Ссылка ".
Переименуйте созданный запрос из параметра файла (2) в преобразование примера файла.
Щелкните правой кнопкой мыши этот новый запрос преобразования примера файла и выберите параметр "Создать функцию ".
Эта операция фактически создаст новую функцию, которая будет связана с запросом преобразования примера файла . Любые изменения, внесенные в запрос " Преобразование примера файла ", будут автоматически реплицированы в пользовательскую функцию. Во время создания этой новой функции используйте файл преобразования в качестве имени функции.
После создания функции вы заметите, что новая группа будет создана для вас с именем функции. Эта новая группа будет содержать:
- Все параметры, на которые ссылались в запросе примера файла преобразования .
- Запрос примера файла преобразования , который называется примером запроса.
- Созданная функция в данном случае — файл преобразования.
Применение преобразований к примеру запроса
Создав новую функцию, выберите запрос с именем "Преобразование примера файла". Теперь этот запрос связан с функцией преобразования файла , поэтому любые изменения, внесенные в этот запрос, будут отражены в функции. Это то, что называется понятием примера запроса, связанного с функцией.
Первое преобразование, которое должно произойти с этим запросом, — это преобразование, которое будет интерпретировать двоичный файл. Вы можете щелкнуть двоичный файл правой кнопкой мыши на панели предварительного просмотра и выбрать параметр CSV, чтобы интерпретировать двоичный файл как CSV-файл .
Формат всех CSV-файлов в папке одинаков. Все они имеют заголовок, охватывающий первые четыре строки. Заголовки столбцов расположены в пяти строках, а данные начинаются с шести строк вниз, как показано на следующем рисунке.
Следующий набор шагов преобразования, которые необходимо применить к файлу преобразования sample :
Удалите первые четыре строки. Это действие избавляет от строк, которые считаются частью раздела заголовка файла.
Дополнительные сведения о том, как удалить строки или отфильтровать таблицу по позиции строки, см. в разделе "Фильтр по позиции строки".
Повышение уровня заголовков. Заголовки для конечной таблицы теперь находятся в первой строке таблицы. Вы можете повысить их уровень, как показано на следующем рисунке.
Power Query по умолчанию автоматически добавит новый шаг "Измененный тип" после продвижения заголовков столбцов, которые автоматически определяют типы данных для каждого столбца. Запрос примера файла преобразования будет выглядеть так, как показано на следующем рисунке.
Дополнительные сведения о повышении и понижении заголовков см. в разделе "Повышение или понижение заголовков столбцов".
Функция файла преобразования зависит от шагов, выполняемых в запросе на пример файла преобразования . Однако при попытке вручную изменить код для функции преобразования файла вы увидите предупреждение, которое считывает The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.
Вызов пользовательской функции в качестве нового столбца
После создания пользовательской функции и всех включенных шагов преобразования можно вернуться к исходному запросу, в котором есть список файлов из папки. На вкладке "Добавить столбец " на ленте выберите "Вызвать пользовательскую функцию " в группе "Общие ". В окне "Вызов пользовательской функции " введите выходную таблицу в качестве имени нового столбца. Выберите имя функции, файл преобразования в раскрывающемся списке "Запрос функции ". После выбора функции в раскрывающемся меню отобразится параметр функции, и вы можете выбрать столбец из таблицы, который будет использоваться в качестве аргумента для этой функции. Выберите столбец Content в качестве значения или аргумента, который будет передан для параметра файла.
После нажатия кнопки "ОК" будет создан новый столбец с именем Output Table . Этот столбец содержит значения таблицы в своих ячейках, как показано на следующем рисунке. Для простоты удалите все столбцы из этой таблицы, кроме "Имя " и "Выводная таблица".
Дополнительные сведения о выборе или удалении столбцов из таблицы см. в статье "Выбор или удаление столбцов".
Функция была применена к каждой строке из таблицы, используя значения из столбца Content в качестве аргумента для функции. Теперь, когда данные были преобразованы в фигуру, которую вы ищете, можно развернуть столбец выходной таблицы , как показано на рисунке ниже, без использования префикса для развернутых столбцов.
Чтобы проверить наличие данных из всех файлов в папке, проверьте значения в столбце "Имя " или "Дата ". В этом случае можно проверить значения из столбца Date , так как каждый файл содержит только данные за один месяц от заданного года. Если отображается несколько файлов, это означает, что вы успешно объединили данные из нескольких файлов в одну таблицу.
То, что вы читали до сих пор, является фундаментальным процессом, который происходит во время работы с файлами объединения , но делается вручную.
Мы также рекомендуем ознакомиться со статьей "Общие сведения о совместном использовании файлов" и "Объединить CSV-файлы", чтобы узнать, как работает взаимодействие с файлами в Power Query и роли, которую играют пользовательские функции.
Добавление нового параметра в существующую пользовательскую функцию
Imagine, что есть новое требование на основе того, что вы создали. Новое требование требует, чтобы перед объединением файлов вы фильтруете данные внутри них, чтобы получить только строки, в которых страна равна Панаме.
Чтобы сделать это требование, создайте новый параметр с именем Market с текстовым типом данных. В поле "Текущее значение" введите значение Панама.
С помощью этого нового параметра выберите запрос " Преобразовать образец файла " и отфильтруйте поле "Страна ", используя значение из параметра Market .
Дополнительные сведения о фильтрации столбцов по значениям см. в разделе "Фильтрация значений".
Применение этого нового шага к запросу автоматически обновит функцию файла преобразования, которая теперь потребует двух параметров на основе двух параметров, которые использует файл преобразования.
Но рядом с запросом CSV-файлов есть знак предупреждения. Теперь, когда функция была обновлена, для нее требуются два параметра. Таким образом, шаг, в котором вызывается функция, приводит к возникновению ошибок, так как только один из аргументов был передан в функцию файла преобразования во время шага вызываемой пользовательской функции .
Чтобы устранить ошибки, дважды щелкните "Вызываемая пользовательская функция " в разделе "Примененные действия ", чтобы открыть окно "Вызвать пользовательскую функцию ". В параметре Market вручную введите значение Панамы.
Теперь вы можете проверить запрос, чтобы убедиться, что только строки, в которых страна равна Панаме , отображаются в конечном результирующем наборе запроса CSV-файлов .
Создание пользовательской функции на основе повторно используемых фрагментов логики
Если у вас есть несколько запросов или значений, требующих одного набора преобразований, можно создать пользовательскую функцию, которая выступает в качестве многократно используемых фрагментов логики. Позже эту пользовательскую функцию можно вызвать для запросов или значений по своему усмотрению. Эта пользовательская функция может сэкономить время и помочь вам управлять набором преобразований в центральном расположении, которое можно изменить в любой момент.
Например, представьте запрос с несколькими кодами в виде текстовой строки и вы хотите создать функцию, которая будет декодировать эти значения.
Сначала у вас есть параметр, имеющий значение, которое служит примером. В этом случае это будет значение PTY-CM1090-LAX.
В этом параметре создается новый запрос, в котором применяются необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-LAX на несколько компонентов:
Ниже показан код M для этого набора преобразований.
Дополнительные сведения о языке формул Power Query M см. в статье Power Query языке формул M.
Затем этот запрос можно преобразовать в функцию, щелкнув запрос правой кнопкой мыши и выбрав команду Create Function. Наконец, можно вызвать пользовательскую функцию в любой из запросов или значений, как показано на следующем рисунке.
После нескольких преобразований вы увидите, что достигли нужных выходных данных и использовали логику для такого преобразования из пользовательской функции.
Просто с помощью редактора Power Query вы создавали формулы Power Query. Давайте посмотрим, как работает Power Query, посмотрев на него. Чтобы узнать, как обновлять или добавлять формулы, просто посмотрите редактор Power Query в действии. Вы даже можете скатить собственные формулы с помощью расширенных редакторов.
Редактор Power Query предоставляет запросы и возможности формирования данных для Excel, которые можно использовать для повторного формирования данных из различных источников. Чтобы отобразить окно редактора Power Query,импортировать данные из внешних источников на Excel, выбрать ячейку в данных, а затем выбрать запрос >Изменить. Ниже следующую сводку основных компонентов.
Лента редактора Power Query, используемая для формировании данных
The Queries pane that you use to locate data sources and tables
Контекстные меню, удобные для быстрого доступа к командам на ленте
Предварительный просмотр данных с результатами действий, примененных к данным
В области Параметры запроса со списком свойств и каждого шага запроса
При этом каждый шаг запроса основан на формуле, которая отображается в панели формул.
Иногда нужно изменить или создать формулу. В формулах используется язык формул Power Query, который можно использовать для создания как простых, так и сложных выражений. Дополнительные сведения о синтаксисе, аргументах, замечаниях, функциях и примерах см. в power Query M formula language.
Используя в качестве примера список футбольного мяча, используйте Power Query, чтобы получить необработанные данные, найденные на веб-сайте, и превратить их в хорошо отформатированную таблицу. Посмотрите, как создаются шаги запроса и соответствующие формулы для каждой задачи в области запроса Параметры в области Примененные шаги и в области формул.
Последовательность действий
Чтобы импортировать данные, выберите данные > из Интернета,введите "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" в поле URL-адрес, а затем выберите ОК.
В диалоговом окне Навигатор выберите таблицу Результаты [Изменить] слева, а затем выберите Преобразовать данные в нижней части. Появится редактор Power Query.
Чтобы изменить имя запроса по умолчанию, в области запроса Параметры в области Свойства удалите "Результаты [Изменить]", а затем введите "ЫК".
Чтобы удалить ненужные столбцы, выберите первый, четвертый и пятый столбцы, а затем выберите главная> удалить столбец > удалить другие столбцы.
Чтобы удалить ненужные значения, выберите Столбец1, выберите Главная> Заменитьзначения , введите "сведения" в поле Значения для поиска, а затем выберите ОК.
Чтобы удалить строки со словом "Год", выберите стрелку фильтра в столбце "Столбец1",снимите его рядом с полем "Год", а затем выберите ОК.
Чтобы переименовать столбцы, дважды щелкните каждый из них, а затем измените "Столбец1" на "Год", "Столбец4" на "Winner" и "Столбец5" на "Итоговая оценка".
Чтобы сохранить запрос, выберите главная >Закрыть & загрузить.
В следующей таблице содержится сводка каждого примененного шага и соответствующей формулы.
Шаг запроса и задача
Подключение к веб-источнику данных
Changed Type
Изменение типов данных (которые Power Query делает автоматически)
Удалены другие столбцы
Удаление ненужных столбцов
Заменено значение
Замена значений для очистки значений в выбранном столбце
Отфильтрованные строки
Фильтрация значений в столбце
Переименованные столбцы
Измененные заглавные столбцы должны быть осмысленными
Важно Будьте внимательны при редактировании действий"Источник","Навигация" и "Измененный тип", поскольку они создаются Power Query для определения и создания источника данных.
Показ или скрытие панели формул
По умолчанию отображается формула, но если она не видна, ее можно отыгрывать.
Выберите Просмотр >макета > формул.
Edit a formula in the formula bar
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
В области Параметры запроса в области Примененныешаги выберите шаг, который вы хотите изменить.
В области формул найдите и измените значения параметров, а затем нажмите ввод. Например, измените эту формулу, чтобы сохранить столбец 2:
Нажмите значок или нажмите ввод, чтобы увидеть новые результаты в режиме предварительного просмотра данных.
Чтобы увидеть результат на Excel, выберите Главная > Закрыть & Загрузка.
Создание формулы в панели формул
Для простого примера формулы преобразуем текстовое значение в нужный с помощью функции Text.Proper.
Чтобы открыть пустой запрос, в Excel выберите Данные> Получить данные > из других источников > Пустой запрос. Дополнительные сведения см. в этойExcel.
Введите в формулу =Text.Proper("text value") , а затем нажмите ввод.
Результаты отображаются в режиме предварительного просмотра данных.
Чтобы увидеть результат на Excel, выберите Главная > Закрыть & Загрузка.
При создании формулы Power Query проверяет ее синтаксис. Однако при вставке, переусортовке или удалении промежуточного шага запроса это может привести к разрыву запроса. Всегда проверять результаты в режиме предварительного просмотра данных.
Важно Будьте внимательны при редактировании действий"Источник","Навигация" и "Измененный тип", поскольку они создаются Power Query для определения и создания источника данных.
Редактирование формулы в диалоговом окне
Этот способ позволяет использовать диалоговое окно, которое зависит от шага. Синтаксис формулы знать не нужно.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
В области Параметры запроса в области Примененные шаги щелкните значок Изменить Параметры действия, который вы хотите изменить, или щелкните его правой кнопкой мыши и выберите изменить Параметры .
В диалоговом окне внести изменения и нажмем ОК.
Вставка шага
После выполнения шага запроса, в который были добавлены данные, шаг запроса добавляется под текущим этапом запроса. но при вставке шага запроса в середине шагов может возникнуть ошибка в последующих действиях. При попытке вставить новый шаг При попытке вставить новый шаг в Power Query отображается предупреждение Вставка шага, при этом изменяются поля, например имена столбцов, которые используются на всех шагах, которые следуют за ним.
В области Параметры запроса в области Примененныешаги выберите шаг, который должен предшествовать новому шагу и соответствующей формуле.
Выберите значок Добавить слева от панели формул. Можно также щелкнуть правой кнопкой мыши шаг и выбрать Вставить шаг после.Новая формула будет создана в формате : =
Введите новую формулу в формате:
Например, предположим, что у вас есть таблица со столбцом Gender и вы хотите добавить столбец со значением "Ms". или "г-н", в зависимости от пола человека. Формула будет:
=Table.AddColumn(, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")
Переусортовка шага
В области Запросы Параметры в области Примененныешаги щелкните правой кнопкой мыши шаг, а затем выберите Вверх илиВниз.
Удаление шага
Щелкните значок слева от шага или щелкните его правой кнопкой мыши и выберите удалить или Удалить до конца. Значок удаления также доступен слева от панели формул.
В этом примере мы преобразуем текст в столбце в нужный с помощью сочетания формул в расширенный редактор.
Например, у вас есть Excel "Заказы" со столбцом ProductName, который нужно преобразовать в нужный пример.
При создании расширенных запросов создается ряд шагов формулы запроса на основе выражения let. Используйте выражение let для назначения имен и вычисления значений, на которые затем ссылается предложение in, которое определяет шаг. Этот пример возвращает тот же результат, что и в разделе "Создание формулы в панели формул".
let
Source = Text.Proper("hello world")
in
Source
Вы увидите, что каждый шаг строится на основе предыдущего шага, ссылаясь на шаг по имени. Напоминаем, что в языке формул Power Query с чувствительностью к делу.
Этап 1. Открытие расширенных редакторов
В Excel выберите Данные> Получить данные > другие источники > пустой запрос. Дополнительные сведения см. в этойExcel.
В редакторе Power Query выберите главная> Расширенный редактор, который откроется с шаблоном выражения let.
Этап 2. Определение источника данных
Создайте выражение let с помощью Excel. Функция CurrentWorkbook: let
Чтобы загрузить запрос на таблицу, выберите Готово ,а затем выберите Главная> Закрыть & Загрузка > Закрыть & Загрузить.
Этап 3. Продвижение первой строки до заглавных
Чтобы открыть запрос, выберем ячейку с данными на > запрос. Дополнительные сведения см. в статьи Создание, загрузка и изменение запроса в Excel (Power Query).
В редакторе Power Query выберите Главная > Расширенный редактор, который откроется с помощью выписки, созданной на этапе 2:Определение источника данных.
Чтобы загрузить запрос на таблицу, выберите Готово ,а затем выберите Главная> Закрыть & Загрузка > Закрыть & Загрузить.
Этап 4. Изменение каждого значения в столбце на правильное
Чтобы открыть запрос, выберем ячейку с данными на > запрос. Дополнительные сведения см. в этойExcel.
В редакторе Power Query выберите Главная> Расширенный редактор, который откроется с заявлением, созданным на этапе 3: Повысить первую строку до заглавных.
Чтобы загрузить запрос на таблицу, выберите Готово ,а затем выберите Главная> Закрыть & Загрузка > Закрыть & Загрузить.
Вы можете управлять поведением панели формул в редакторе Power Query для всех книг.
Отображение или скрытие панели формул
Выберите Параметры> файла и Параметры > параметры запроса.
В левой области в области GLOBALвыберите Редактор Power Query.
В области справа в области Макет выберитеили отобразить формулу.
Включить или отключить M Intellisense
Выберите Параметры> файла и Параметры > параметры запроса .
В левой области в области GLOBALвыберите Редактор Power Query.
В области справа в области Формула выберитеили отберите включить M Intellisenseв панели формул, в расширенный редактор и в диалоговом окке настраиваемого столбца .
Примечание Изменение этого параметра вступает в силу при следующем переходе в окно редактора Power Query.
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
Работа с пользовательской функцией, как правило, включает три этапа:
- Один раз выполните сценарий обработки.
- Преобразуйте сценарий в функцию.
- Вызовите функцию из другого запроса.
Вернемся к сценарию из главы 18, где вы импортировали файл расписания 2015-03-14.txt. Там вы уже создали код для импорта и обработки. Теперь вы хотите применить ту же логику к подобным файлам. Однако вместо того, чтобы изменить Источник, вы хотите объединить все файлы (и любые другие, которые позже будут добавлены в папку). Для этого пригодится пользовательская функция.
Рис. 21.1. Ошибка из-за неверного пути к файлу; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Построение сценария
Рис. 21.2. Окно импорта файла
Нажмите Ok. Вспомните главу 18: запрос загружает записи из текстового файла, извлекает имена сотрудников в новый столбец и изменяет тип данных:
Рис. 21.3. Финальный вид запроса
Преобразование запроса в функцию
- Придумайте имя переменной, которая будет содержать данные, подлежащие изменению.
- Поместите в первую строку запроса код variable_name(имя_переменной) =>
- Найдите строки запроса, где указан путь к файлу, и замените его именем переменной.
Поскольку цель – преобразовать одноразовый сценарий в функцию, в которой вы сможете динамически обновлять путь к файлу, логично дать имя filepath. Или выберите иное имя, лишь бы оно не содержало пробелов или специальных символов.
Проводник Windows более «лоялен» к тому, какие символы могут содержаться в пути к папке и имени файла. Power Query более требователен. Например, нельзя использовать восклицательный знак в качестве первого символа.
Отредактируйте запрос, чтобы превратить его в функцию. В Excel пройдите по меню Данные –> Запросы и подключения. В области Запросы и подключения кликните правой кнопкой мыши на запросе Timesheet –> Изменить. В редакторе Power Query пройдите по меню Главная –> Расширенный редактор. Поместите курсор перед оператором let. Введите (filepath)=>
Нажмите Enter. Начало вашего кода должно выглядеть так:
Рис. 21.4. Переменная filepath в первой строке
Теперь просмотрите код, и замените путь к файлу именем переменной:
- Найдите путь к файлу в строке, которая начинается с Source
- Выделите путь к файлу без кавычек
- Нажмите Ctrl+C, чтобы скопировать путь в буфер для последующего использования
- Удалите кавычки, окружавшие путь к файлу
- Введите вместо пути к файлу имя переменной filepath
Рис. 21.4а. Переменная filepath вместо пути к файлу
В расширенном редакторе нажмите Готово, и ваш запрос резко изменится:
Рис. 21.5. Куда подевалась таблица?
Теперь это функция, а не запрос. А функция просит указать переменную. В области ПРИМЕНЕННЫЕ ШАГИ вы потеряли все элементы. Не волнуйтесь: они все еще там. Просто, вы не можете их увидеть.
Тестирование функции
Рис. 21.6. Данные, загруженные на основании динамического указания пути к файлу
В поле ПРИМЕНЕННЫЕ ШАГИ пока отражается только один шаг (1). А слева появилось поле Запросы (2). В нем показано, что вы вызвали функцию.
Такой вызов функции хорош для тестирования, но не удобен при вызове функции из другого места. Теперь, когда вы протестировали функцию, удалите этот шаг: кликните правой кнопкой мыши на Вызванная функция –> Удалить. Переименуйте запрос fnGetTimesheet. Главная –> Закрыть и загрузить. Обратите внимание, что таблица, которая находилась в Excel на листе Timesheet, исчезнет. Это связано с тем, что запрос был изменен на функцию. А функции могут быть созданы только в режиме подключения соединения:
Рис. 21.7. Запрос теперь является функцией
Вызов функции
Теперь вам нужно вызвать эту функцию из другого запроса. Поскольку конечной целью является консолидация всех файлов из папки, можно запустить новый запрос для импорта всех расписаний сразу. В Excel пройдите по меню Данные –> Получить данные –> Из файла –> Из папки. Найдите и выберите папку Source Files. Нажмите Ok. В окне предварительного просмотра нажмите Преобразовать данные. Выберите столбец Folder Path –> удерживая нажатой Ctrl, выберите столбец Name (именно в таком порядке) –> Щелкните правой кнопкой мыши –> Удалить другие столбцы. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Введите формулу:
Нажмите Ok. Хитрость в том, чтобы использовать только что определенную функцию, а полный путь к файлу получить путем конкатенации столбцов пути и имени – [Folder Path]&[Name]. Затем функция выполнит все шаги, и вернет таблицу в пользовательский столбец. Эта таблица представляет собой обработанный функцией набор данных:
Рис. 21.8. Предварительный просмотр таблицы
Теперь вы можете удалить столбцы Folder Path и Name и кликнуть на кнопке Развернуть (1 на рис. 21.9), тем самым консолидируя содержимое файлов в одной таблице. Отключите параметр Использовать исходное имя столбца как префикс (2), снимите галочки с двух последних столбцов. (3). Нажмите Ok.
Рис. 21.9. Настройка параметров команды Развернуть
Отладка пользовательских функций
Одна из проблем использования пользовательских функций заключается в том, что вы теряете возможность проходить по шагам сценария. Это делает их отладку сложной задачей. Хотя это и не идеально, можно преобразовать функцию обратно в запрос. В таком временном состоянии теряются возможности использования функции в других запросах (поскольку это уже не функция).
Закомментируйте строку, которая превращает запрос в функцию. Присвойте переменной значение. Добавьте новый шаг после строки let:
Строка должна заканчиваться запятой. Откройте функцию fnGetTimesheet в редакторе Power Query –> Главная –> Расширенный редактор. Внесите два изменения:
filepath = " C:\yourfilepath\Source Files\2015-03-14.формат txt " ,
Помните про запятую в конце строки:
Рис. 21.10. Функция преобразована обратно в запрос
При нажатии Готово можно перейти к пошаговому выполнению запроса, и скорректировать сценарий при необходимости. Пока ваша функция находится в режиме отладки, все последующие запросы, которые ссылаются на нее, не будут работать.
Чтобы превратить запрос обратно в функцию, вам снова нужно отредактировать код M:
- Удалите символы // из первой строки.
- Добавьте символы // перед строкой, которая в данный момент объявляет переменную filepath (третья строка на рис. 21.10).
Нажмите Готово. После этого, ваша функция возобновит свой обычный метод работы, и все запросы, использующие эту функцию, смогут использовать ее снова.
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
Пользовательский интерфейс Power Query позволяет выполнять огромное число операций. Но наверняка возникнут моменты, когда вам потребуется что-то сделать, что не встроено в интерфейс. Вот мы и добрались до языка программирования Power Query: M.
Хотя некоторые аспекты M могут быть довольно сложными, есть простой способ получить представление о языке, начав с формул в пользовательских столбцах. Поскольку Power Query был создан для профессионалов Excel, можно было ожидать, что его язык будет подобен языку формул Excel. К сожалению, это не так.
Рис. 17.1. Интерфейс создания пользовательского столбца
Создание пользовательских столбцов
Окно создания настраиваемого столбца содержит три важные части (см. 17.1):
- Имя столбца
- Доступные столбцы: здесь перечислены имена всех столбцов в запросе. Двойной щелчок любого элемента в этом поле помещает его в область формулы с правильным синтаксисом для ссылки на поле.
- Пользовательская формула столбца – место, где вы записываете формулу.
Некоторые функции, доступные в Excel, могут использоваться и здесь. Например, чтобы объединить два текстовых поля, постройте формулу следующим образом:
К сожалению, Power Query не имеет подсказок, чтобы выяснить, какие функции можно использовать. Но… в окне Настраиваемый столбец есть гиперссылка Сведения о формулах Power Query (см. рис. 17.1). Щелкнув на нее, вы попадете на страницу с подробным каталогом функций, правда, на английском языке. В отличие от Excel, но аналогично Power Pivot, функции Power Query не русифицированы.
Подводные камни формул на языке М
Power Query и Excel существенно различаются в том, как они обрабатывают входные данные.
Рис. 17.2. Различия Power Query и Excel в обработке данных
Чувствительность к регистру. Запомните, что в 99% случаев первая буква каждого слова в формуле на языке М – заглавная, а остальные – строчные. В то время как Excel не заботится, какие буквы вы используете и преобразует формулы в верхний регистр по умолчанию, Power Query просто возвращает ошибку.
База 0 против базы 1. Если бы вас спросили о номере позиции буквы x в слове Excel, вы сказали бы 2. Это логично, и так считает программа MS Excel. Но Power Query скажет, что буква x в слове Excel занимает позицию 1.
Преобразование типов данных. В Excel вы можете добавить единицу к дате, что изменит дату на один день. В Power Query, если дата отформатирована с типом Даты, необходимо использовать специальную формулу чтобы добавить к ней день. Если вы попытаетесь использовать ту же формулу для добавления дней к числу, Power Query вернет ошибку. Это означает, что перед использованием столбцов в формулах необходимо явно задавать в них тип данных.
В Excel вы можете объединить две ячейки вместе с помощью функции конкатенации &. Содержат ли ячейки текст или числа, не важно. Excel неявно преобразует их в текст, а затем объединяет вместе:
Рис. 17.3. Неявное преобразование данных в Excel: число и текст, преобразованные в текст
Создайте на основе двух первых столбцов Таблицы Excel запрос, а затем внутри Power Query создаете пользовательский столбец, используя формулу: =[Column1]&[Column2]:
Рис. 17.4. Power Query не может соединить число и текст вместе
Чтобы устранить эту проблему, необходимо сначала преобразовать тип данных Столбец1 в текст, а уже затем создать пользовательский столбец:
Рис. 17.5. Два текстовых столбца объединить можно
При явном преобразовании данных в столбце 1 в текстовое значение конкатенация будет работать так, как вы изначально предполагали:
На самом деле существует два способа работы с типами данных в Power Query:
- Установите тип данных для столбца, на который вы ссылаетесь, прежде чем использовать его в пользовательской функции.
- Используйте функцию преобразования типа данных, чтобы принудительно преобразовать входные данные в требуемый тип.
Функции преобразования типов данных
Существует несколько функций преобразования типов данных.
Преобразование в текст. Если вам нужно преобразовать значения в столбце в текст, можно использовать универсальную функцию Text.From(). Если же вы хотите подчеркнуть тип преобразуемых данных, также в вашем распоряжении есть: Date.ToText(), Time.ToText(), Number.ToText(). Имейте в виду, что Text.From() преобразует любой тип данных в текст, в то время как Date.ToText() не преобразует число в текст.
Даты. Данные, похожие на даты, могут поступать в формате чисел или текста. Для их преобразования есть две функции: Date.From() и Date.FromText(). Опять же, Date.From() справится с преобразованием в формат даты, как чисел, так и текста.
Время. Значения времени могут поступать как в виде чисел, так и в виде текста. Опять же, есть две функции для них: Time.From() и Time.FromText().
Длительность – это разница между двумя значениями даты/времени: Duration.From() и Duration.FromText().
Числа. Имеется универсальная функция Number.From() и несколько специальных. Для чисел из текста Number.FromText(), для десятичных чисел Decimal.From(), целых чисел Int64.From(), валюты Currency.From().
Сравнение текстовых функций Excel и Power Query
Если вы работали с текстовыми функциями Excel, то привыкли использовать их для извлечения элементов текста из данных. В Power Query текстовые функции работают иначе. Рассмотрим пять наиболее часто используемых текстовых функций Excel, и их аналоги в Power Query. Откройте файл 5 Useful Text Functions.xlsx. Каждый из примеров в этом разделе начинается с набора данных:
Рис. 17.6. Пример данных
В августе 2015 года команда Power Query добавила возможность извлечения первого, последнего и ряда символов на вкладку Преобразование. Несмотря на это, ниже рассматривается процесс извлечения текста с помощью языка M, что позволит глубже познакомиться с языком, и создавать более надежные решения, чем те, что могут быть созданы с помощью команд пользовательского интерфейса.
Итак, что поместить данные, представленные на рис. 17.6, в Power Query, кликните на любой ячейке в диапазоне А1:В8 –> Данные –> Из таблицы/диапазоне. Подтвердите создание Таблицы с заголовком. В окне редактора Power Query переименуйте запрос pqLeft. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqLeft(x,4). Введите формулу: =LEFT([Слово],4). Вроде бы, это должно сработать:
Рис. 17.7. Power Query не находит синтаксических ошибок
Однако, после нажатия Ok, появляется ошибка:
Рис. 17.8. Формула =LEFT([Слово],4) не работает
В Power Query используется иной синтаксис =Text.Start(text,num_chars). Отредактируйте формулу. В области ПРИМЕНЕННЫЕ ШАГИ кликните на шестеренку справа от строки Добавлен пользовательский столбец, и в окне Настраиваемый столбец введите формулу: =Text.Start([Слово],4). Не забывайте, что формулы в Power Query чувствительны к регистру: Text.start и TEXT.START вернут ошибку. Нажмите Ok:
Рис. 17.9. Функции ЛЕВСИМВ() в Excel соответствует Text.Start() в Power Query
Теперь вы можете завершить запрос: Главная –> стрелочка вниз возле кнопки Закрыть и загрузить –> Закрыть и загрузить в… –> Только создать подключение.
Никогда не используйте имя функции Excel в качестве имени запроса Power Query. Если бы вы назвали запрос ЛЕВСИМВ, вы бы получили ошибки в формулах Excel исходной Таблицы. Имена таблиц обрабатываются перед функциями.
Рис. 17.10. Не давайте запросам имена, совпадающие с именами функций Excel; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Рис. 17.11. Соответствие текстовых функций Excel и Power Query
Первые две функции из таблицы аналогичны только что рассмотренной Text.Start(). Использование двух последних функций требует небольшой коррекции в связи с тем, что Power Query за точку начала отсчета берет ноль. Также обратите внимание на следующее различие: аргумент искомый текст является первым в функции НАЙТИ(), и – вторым в функции Text.PositionOf().
Аналог функции НАЙТИ
В файле 5 Useful Text Functions.xlsx перейдите на лист FIND. Кликните на одной из ячеек таблицы, пройдите по меню Данные –> Из таблицы/диапазона. В окне редактора Power Query переименуйте запрос pqFind. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqFind(x, " o " ). Введите формулу: =Text.PositionOf([Word], " o " ). Нажмите Ok.
Рис. 17.12. Результат не вполне согласуются с Excel
Возвращаемые значения, следуют базовому правилу. В первой строке буква F идет под номером 0. Измените формулу, добавив 1: =Text.PositionOf([Word], " o " )+1.
Рис. 17.13. Есть совпадение с Excel, а вместо ошибок выводится значение ноль
Аналог функции ПСТР
В файле 5 Useful Text Functions.xlsx перейдите на лист MID. Кликните на одной из ячеек таблицы, пройдите по меню Данные –> Из таблицы/диапазона. В окне редактора Power Query переименуйте запрос pqMid. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqMid(x,5,4). Введите формулу: =Text.Range([Word],5,4). Нажмите Ok. Результат не соответствует ожиданиям:
Рис. 17.14. Несколько результатов адекватно, но почему не все?
Рис. 17.15. Уже лучше, но всё еще ошибки в двух последних строках
Одна из замечательных особенностей функции Mid (ПСТР) Excel заключается в том, что вас не волнует, сколько символов осталось в текстовой строке. Если конечный параметр больше, чем количество оставшихся символов, он просто вернет все оставшиеся символы. Не таков Power Query. Вам нужно дополнить формулу проверкой: вы хотите вернуть четыре символа или меньше, до конца текстовой строки. Для этих целей подойдет функция List.Min (подробнее о ней вы узнаете из главы 20). Вместо того, чтобы пытаться встроить эту функцию в формулу столбца pqMid(x,5,4), создайте еще один пользовательский столбец с формулой =List.Min().
Рис. 17.16. В отдельном столбце определено количество оставшихся символов
Несколько слов о том, как работает формула:
- Text.Length([Word])-(5-1) подсчитывает длину слова в столбце Word и вычитает начальную позицию. Вы использовали выражение (5-1), чтобы подчеркнуть, что хотели взять пятый символ, но исправили формулу для базы 0 (можно использовать и 4).
- Последняя четверка в формуле – максимальное количество символов, которые вы хотите вернуть
- Для того, чтобы использовать их в функции List.Min() они должны быть окружены фигурными скобками и разделены запятыми.
Теперь вы можете отредактировать формулу в столбце pqMid(x,5,4) =Text.Range([Word],5-1, List.Min())
Рис. 17.17. Всё верно, кроме последней строки
Теперь вы можете удалить вспомогательный столбце Пользовательская и загрузить запрос в Таблицу на лист Excel. А как же ошибка в последней строке. Не страшно. Потому что ошибки в Power Query будут показываться в Excel, как пустые ячейки:
Рис. 17.18. Ошибки исчезают при загрузке в Таблицу
Очень жаль, что не все функции в Power Query эквивалентны функциям в Excel. Особенно учитывая, что Power Query – это инструмент для пользователей Excel. Надеемся, что это изменится в будущих версиях. Мы хотели бы видеть новую библиотеку функций Power Query, которые позволяют переносить существующие знания из Excel в Power Query без необходимости изучать новые функции и новый синтаксис.
В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.
Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.
И вот под катом вы можете найти подробности всего этого великолепия возможностей.
Совместимость и технические подробности
Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).
Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.
Как оно работает
После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.
В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.
Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.
Возможности Power Query
У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.
Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.
Объединение файлов лежащих в папке
Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка
Текстовые функции
- Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
- Изменить регистр ячеек в столбце
- Подсчитать количество символов в ячейках столбца.
Числовые функции
К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:
- Арифметические операции
- Возводить в степени, вычислять логарифмы, факториалы, корни
- Тригонометрические операции
- Округлять до заданных значений
- Определять четность и т.д.
Функции для работы с датами, временем и продолжительностью
К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:
- Автоматическое определение формата вписанной даты (в excel c этим большая боль)
- Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.
Unpivot — Pivot
В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка
Операция Merge — смерть ВПР
Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.
Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.
Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка
Подключение к различным базам данных. Query Folding.
Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.
А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.
Язык программирования “М”
Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.
На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка
Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:
Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.
Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api
Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.
Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным :)
Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.
Еще раз про повторяемость и про варианты применения
Как я уже писал выше, скрипт Power Query представляет собой повторяемую последовательность манипуляций, применяемых к данным. Это значит, что однажды настроив нужную вам обработку вы сможете применить ее к новым файлам изменив всего один шаг в скрипте — указав путь к новому файлу. Благодаря этому можно избавиться от огромного количества рутины и освободить время для продуктивной работы — анализа данных.
Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.
- разбираю семантику для Толстых проектов,
- Делаю частотные словари,
- Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
- Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
- Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
- Делаю аудит контекстной рекламы на данных из K50 статистика,
- И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды
Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.
Пару слов про локализацию
На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.
Читайте также: