Как сделать функцию в power query

Добавил пользователь Евгений Кузнецов
Обновлено: 05.10.2024

Доброго времени суток!

Который день пытаюсь построить цепочку функций в Power Query, но так и не смог, т.к. логика построения формул Excel в PQ не очень работает.
Вопрос про альтернативу СЧЕТЕСЛИМН в Power Query.

Вот есть массив (файл прилагаю) со столбцом Count, требуется по аналогии создать столбец в PQ для каждой позиции в массиве.
Спасибо заранее.

Доброго времени суток!

Вот есть массив (файл прилагаю) со столбцом Count, требуется по аналогии создать столбец в PQ для каждой позиции в массиве.
Спасибо заранее. Digitalizer

Вот есть массив (файл прилагаю) со столбцом Count, требуется по аналогии создать столбец в PQ для каждой позиции в массиве.
Спасибо заранее. Автор - Digitalizer
Дата добавления - 23.05.2019 в 10:38

В программе Power BI Desktop Power Query встроенный модуль. В справке и интерфейсе этот модуль называется Редактор Запросов или Query Editor . Power Query это основной инструмент для получения данных в модель данных Power BI desktop. Как показано на скриншоте ниже все выделенные кнопки относятся к редактору запросов Power Query.

Power Query в Excel 2010, 2013¶

Power Query в Excel 2016, 2019, 365¶

В зависимости от версии подписки Excel функционал Power Query может различаться.

Зачем нужен Power Query¶

Power Query нужен для удобного преобразования данных (ETL-процесса).

Согласно википедии - ETL (от англ. Extract, Transform, Load) – процесс в управлении хранилищами данных, который включает в себя:

извлечение данных из внешних источников;

их трансформация и очистка, чтобы они соответствовали потребностям бизнес-модели;

и загрузка их в хранилище данных.

Power Query отлично подходит для задач:

подключения к разнообразным источникам (различным типам файлов, api, базам данных и т.п.);

для удобного и гибкого преобразования данных в необходимый формат;

Для создания повторяемых последовательностей обработки данных.

Что такое запрос (Query)¶

Запрос (Query) это программа на языке M, задающая последовательность обработки данных.

В своем теле запрос может обращаться к неограниченному количеству источников данных (включая другие запросы). Результат выполнения запроса может иметь любой из поддерживаемых структурированнных или примитивных типов данных, например:

запись - record и т.д.

Список из всех запросов в Excel можно увидеть в разных местах.

В списке из запросов книги (Queries Pane) интерфейсе Excel 2016:

В интерфейсе самого Power Query:

У каждого запроса есть свое имя.

Имя запроса можно увидеть в нескольких местах. Там же его можно изменить:

В интерфейсе Excel 2016.

В интерфейсе Power Query.

По имени запроса можно обращаться к результатам этого запроса из других запросов.

Действия над запросами (по правому щелчку мыши на них)¶

Duplicate (Дублировать)¶

Reference (Сослаться)¶

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

Шаг (Step)¶

Отдельный этап обработки данных в рамках конкретного Запроса.

Запрос состоит из шагов и включает как минимум один шаг.

На каждое примененное действие в интерфейсе создается новый шаг.

Список шагов конкретного запроса можно посмотреть в правой части экрана в панели настроек Запроса.

Каждый шаг это отдельная переменная, расположенная на отдельной строчке кода в скрипте запроса на языке программирования M.

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

А также перетаскивая шаги в списке.

Параметры в Power Query¶

Получение данных в Power Query¶

Чтобы начать работать с Power Query, необходимо настроить получение данных из какого-либо источника. Сделать это можно из интерфейса Power Query в Power BI по нажатию на кнопку Get Data.

В Excel 2010-2013 сделать это можно нажав на кнопки с указанием различных источников на панели Ribbon.

А также из интерфейса Power Query в Excel.

Типы данных в Power Query¶

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

Примитивные типы данных Power Query¶

Decimal number - десятичное число Number.Type или type number

Time - время Time.Type или type time

Date - дата Date.Type или type date

Date / Time - дата / время DateTime.Type или type datetime

Date / Time / Timezone - дата / время / часовая зона DateTimeZone.Type или type datetimezone

Duration - длительность Duration.Type или type duration

Fixed Decimal number - десятичное округленное до 4 знака Currency.Type

Whole number - целое число Int64.Type

Text - текст Text.Type или type text

True/False - истина / ложь

Binary - двоичный код (например, изображение в формате bmp)

Percentage - проценты Percentage.Type

Структурированные типы данных в Power Query¶

List - список - список из элементов Чисел со значениями 1, 2, 4

Record - запись [field1 = "текст в кавычках", field2 = "текст в кавычках2"]

Задание типов данных для столбцов в Power Query¶

Присвоенные отдельным столбцам типы данных определяют операции, которые применимы к ним. Если выбрать столбец, то примененные к нему типы данных можно увидеть в следующих местах:

Типы данных столбцов таблицы обозначаются иконками в области заголовков:

Автоматическое определение типов данных для столбцов¶

автоматически определять типы данных

Создание дубликата столбца¶

Команда на Ribbon:

Создание дубликата столбца

Команда в контекстном меню:

Создание дубликата столбца команда в контекстном меню

Переименование столбцов¶

Чтобы переименовать столбец нужно дважды щелкнуть на его названии:

Переименование столбцов Power Query

Remove Other Columns - удаление прочих столбцов¶

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

Remove Other Columns - удаление прочих столбцов

Split Column by Delimeter - Разделить текстовый столбец по разделителю¶

Кнопка на Ribbon - Split Column

Split Column by Delimeter - Разделить текстовый столбец по разделителю

В контекстном меню, по щелчку на заголовок столбца.

Разделить столбец по произвольному разделителю

Указать максимальное количество столбцов

Действия над таблицами и столбцами таблиц¶

Append - добавление одной таблице к другой таблице¶

Из интерфейса Power Query:

Из интерфейса Excel:

Merge - соединение данных одного запроса с другим запросом по общему ключу (аналог ВПР)¶

Начало операции из интерфейса Power Query:

Начало операции merge из интерфейса Excel:

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

Соединение данных по составному ключ в Power Query¶

После нажатия на кнопку OK мы видим новый столбец с кнопкой

Нажимаем на кнопку, раскрываем столбец и выбираем желаемую операцию

Expand - развернуть данные из выбранных столбцов

Aggregate - подсчитать данные в конкретных столбцах

Важно помнить что типы данных у ключевых столбцов (в обеих таблицах) должны быть одинаковыми.

Команда Group by (сгруппировать по полю)¶

Команду можно вызвать по клику на кнопку на панели Ribbon

Также команду можно вызвать из контекстного меню (если нажать правой кнопкой на заголовке столбца)

Интерфейс команды Group By с комментариями представлен на скриншоте ниже:

Добавление нового столбца в Power Query¶

if then else условия¶

Для выбора действия в зависимости от условия в Power Query используется структура с оператором if then else

if [столбец1] 0 then [столбец2] else [столбец3]

Условный столбец (Conditional column)¶

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

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

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

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

Необходимо щелкнуть правой кнопкой на заголовке столбца

Выбираем страну, где в качестве разделителя используется точка (например, USA)

Удалить дубликаты в столбцах¶

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

Удалить дубликаты строк в таблице можно нажав на кнопку в левом верхнем углу таблицы предпросмотра.

Count rows - Подсчитать количество строчек в текущей таблице¶

Извлечение шагов в отдельный запрос¶

ввести имя нового запроса, который будет создан на основе предыдущих шагов

Функция Сохранить строки (Keep Top Rows)¶

Функция Сохранить ошибки (Keer Errors)¶

Функция Заменить ошибки в столбце (Replace Errors)¶

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

Получение данных из различных источников¶

Получение данных из файлов¶

Получение данных из текстовых файлов (csv, tsv, txt и т.д.)¶

При получении данных из текстового файла в Power Query открывается окно мастера настроек импорта файла.

Извлечение данных из файлов лежащих в папке¶

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

В появившемся окошке предпросмотра данных жмем на кнопочку edit.

Получение данных из интернета¶

Права доступа, Formula.Firewall¶

При работе в Power BI, при обращении к внешним источникам данных вроде различных API могут возникать ошибки вроде: OLE DB or ODBC error: [information is needed in order to combine data]

или Formula.Firewall: Query is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination

Это ошибки, которые возникают из-за встроенного в Power BI Fomrula.Firewall - механизма, который следит, чтобы данные из Power BI передавались только согласно выставленным правилам доступа.

то есть Power Bi пытается защитить нас, чтобы мы случайно не отправили какие-либо данные (вроде токена) на сервер-злоумышленника.

ignore privacy level settings

Эта статья о работе надстройки Power Query к Excel 2010 и Excel 2013, редактора запросов Power BI и группы команд “Get & Transform” (“Получить и преобразовать”) в Excel 2016. Надеюсь, когда-нибудь эта чехарда закончится и мы сможем говорить просто Power Query.

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

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

Промежуточные и дополнительные результаты запросов

Представьте, что в процессе сложных преобразований запроса Query1 последним шагом под названием ResultTable мы получили нужный результат (таблицу), которую мы хотим загрузить в модель данных. Одним из промежуточных шагов в нашем запросе был расчет какой-то величины ValueX , и мы хотели бы использовать ее в других выражениях или запросах:

Нам в итоге нужен и ValueX , и, конечно же, ResultTable .

У нас есть как минимум три способа это сделать:

Разбить запрос в нужной точке на два – в одном получим ValueX , в другом, ссылающемся на него – ResultTable .
Вернуть сразу несколько значений ( ResultTable и ValueX ) в виде записи или списка.
Вернуть одно значение ResultTable , и ValueX в виде метаданных.

Разбить запрос на части

Извлечь предыдущие шаги в новый запрос

При помощи этой команды все шаги до текущего будут выделены в новый запрос, а все шаги начиная с текущего – останутся в прежнем запросе, причем в качестве источника для прежнего запроса будет использовано имя нового запроса. Имя для нового запроса будет задано при разделении. Таким образом, мы можем дать новому запросу имя GetValueX, и использовать его результат как для продолжения преобразований, так и в качестве источника для других запросов.

Разделяем запросы на несколько

Этот подход – наиболее правильный и логичный, если мы можем непосредственно использовать ValueX для получения ResultTable (то есть ValueX содержит всю необходимую информацию для получения ResultTable ).

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

Но если промежуточный шаг, который мы хотим извлечь из запроса, содержит в себе только часть нужных данных, или вообще скалярное значение (например, мы рассчитали минимальную дату по столбцу, и в дальнейшем планируем ее использовать в других запросах) нам придется разбивать запрос немного ранее, и дублировать получение ValueX в разных запросах:

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

На этом рисунке зеленая ветвь обозначает наш основной запрос, который мы разбили на два новых заранее, до шага получения искомого значения ValueX . В красной ветке мы ссылаемся на Query1, чтобы получить отдельным запросом ValueX для использования в запросах Query2 и Query3.

А теперь представьте, что нам нужно получить еще и ValueY , и ValueZ , и еще несколько промежуточных значений (что является вполне реальным сценарием, если на входе у нас один большой массив данных, из которого мы строим модель). Цепочка зависимостей запросов разрастается, что может привести к самым разным последствиям – от неудобства ориентирования в большом документе и до количества обращений Power Query к источнику данных. Иногда этого избежать невозможно (да и не нужно), но иногда городить целый огород для того, чтобы воспользоваться всего одним числом – совершенно избыточно.

Вернуть результат в виде записи или списка

Этот вариант требует базового знания основных составных типов данных Power Query и того, как обращаться к ним или их частям.

В таком случае для получения значения А нам нужно написать MyList < 0 >, а для получения B – MyList < 1 >, где число в фигурных скобках – индекс элемента, начиная с 0. Как видите, порядок элементов в списке важен.

Тогда для получения значения A надо написать MyRecord [ Give_me_A ] , а для получения B – соответственно MyRecord [ Give_me_B ] . В этом случае порядок полей в записи уже не важен.

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

то мы можем добавить еще один шаг:

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

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

Как получить статистику из Яндекс.Директ и сэкономить время, обновляя данные сразу в Excel и Power BI? Узнайте из новой статьи Ильи Назарова – менеджера отдела рекламы Digital Lab.

Мастер отчетов в Яндекс.Директ – замечательная штука! Если вы им уже пользовались, то наверняка добрым словом вспоминали отчеты Google, которые запоминают комбинации нужных параметров. В Яндексе же приходится тратить время и каждый раз с нуля выбирать срезы, столбцы, группировки…

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

запоминание нужных параметров отчета: срезов, группировок, детализаций;
обновление данных прямо в книге Excel (или проекте Power BI) без необходимости каждый раз заходить в Яндекс.Директ;
экономию времени и бесценный опыт.

Некогда точить топор, надо рубить

Швейцарский нож любого специалиста по контекстной рекламе – Microsoft Excel, поэтому буду показывать на его примере. Скриншоты из версии 2019, а функционал, который предстоит использовать, есть в версиях начиная с 2016-й. Для версий 2010–2013 скачайте и установите надстройку Power Query с официального сайта Microsoft.

В Power BI функционал доступен во всех версиях, а делается все аналогично.

ВАЖНОЕ замечание! Яндекс пока не предлагает решений, позволяющих получать данные в Excel и Power BI по API, поэтому в статье используется разработанный нами скрипт. Техподдержка Яндекса не будет консультировать вас по его работе! Скрипт полностью функционален и соответствует нашим требованиям. Мы готовы рассказать, как им пользоваться. В дальнейшем вы сможете самостоятельно изменять его функционал так, как посчитаете нужным.

Запустится редактор Power Query:

После этого вы увидите интерфейс функции. Если не увидели – значит где-то ошиблись. Внимательно повторите предыдущий шаг!

Инструкция по использованию

Интерфейс функции, получающей статистику Яндекс.Директ, выглядит так:

Токен доступа к данным

Для функции заданы параметры по умолчанию, поэтому для запуска достаточно указать авторизационный токен (поле 6). Я подробно рассказывал про него в статье о работе с Яндекс.Аудиториями. Можно использовать тот же токен. Если у вас его нет, получите новый на сервере авторизации Яндекса по этой ссылке. Убедитесь, что вы авторизованы в нужном аккаунте Яндекса, у которого есть доступ к Яндекс.Директ.

Укажите способ подключения

Если вы еще не работали с API Яндекс.Директ, то увидите вот такое предупреждение о необходимости указать учетные данные:

Промежуточные статусы

Отчет готов

Итак, отчет готов:

В книге Excel появится новый лист, куда автоматически загрузятся данные.

Теперь книгу можно сохранить.

Обновление отчета

You’re all set! Части читателей этой информации будет достаточно. А дальнейшее чтение предлагаю тем, кому пока не хватает навыков работы в Power Query.

Кастомизация отчета

Допустим, вы хотите получить отчет не за последние 30 дней по умолчанию, а за другой период.

Фиксированные даты начала и окончания

При вызове функции вы можете указать точную дату начала периода отчета и точную дату его окончания. Строка вызова функции будет выглядеть так (можно редактировать как формулу Excel):

Частичная автоматизация периода

Если вы хотите получить данные, например, с 3 апреля 2021-го по сегодняшнее число, то строка вызова функции будет выглядеть так:

Полная автоматизация периода

Если у даты начала вы укажете значение null, то туда автоматически подставится число, которое было 30 дней назад. Что делать, если эта дата должна меняться динамически, но нужен, к примеру, 15-дневный период?

Узнайте текущее число.
Сместитесь на нужное количество дней в прошлое.
Создайте новый пустой запрос.

В название каждой функции ниже я вставил ссылку на официальную документацию на сайте Microsoft – переходите и получайте дополнительную информацию.

Введите формулу DateTime.LocalNow() в поле формул и нажмите Enter на клавиатуре.

Формула покажет текущие дату и время. Для вызова функции нам нужна только дата. Извлечь дату из даты и времени можно так:

Power Query запустит функцию Date.From, которая принимает только один параметр – значение даты или даты и времени.

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

Модифицируйте формулу следующим образом:

Укажите название функции Date.AddDays, в круглых скобках перечислите параметры ее вызова:

Date.From(Источник) – функция, полученная на предыдущем шаге (извлекает дату из значения даты и времени);
-15 – количество дней, на которое нужно сместиться относительно указанной даты.

Если сместиться нужно не на дни, используйте аналогичные функции Date.AddWeeks (смещение на целое число недель), Date.AddMonths (месяцев), Date.AddQuarters (кварталов) и Date.AddYears (лет).

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

Переименуйте запрос, чтобы понимать, какие данные в нем находятся:

Должно получиться так:

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

Возможные проблемы и их решения

Отчет с таким названием уже сформирован

Забыли указать логин рекламодателя

Если вы получили токен для аккаунта агентства, внутри которого существуют рекламодатели, то при вызове функции нужно указать название аккаунта рекламодателя (не агентства), который совпадает с адресом электронной почты на Яндексе.

Если вы не агентство (или агентство, но получили токен непосредственно для аккаунта рекламодателя), то логин указывать не нужно.

Заключение

Если у вас есть вопросы, замечания и предложения по работе скрипта, – пишите в комментарии: выслушаем, ответим, посоветуем, доработаем!

Читайте также: