Power Query, он же редактор запросов (Query Editor)

Power Query в Power BI

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

../_images/PowerQueryButtonsInPowerBI2021-01-13_15-05-50.png

Power Query в Excel 2010, 2013

Также, Power Query это надстройка над MS Excel 2010 - 2013. Она устанавливается дополнительно. Скачать надстройку можно по ссылке. На панели Ribbon в Excel 2010 и 2013 Power Query посвящена отдельная вкладка.

../_images/PowerQueryInExcel2013.png

Power Query в Excel 2016, 2019, 365

В Excel 2016 и последующих версиях Power Query встроена. Найти ее можно на вкладке Data (Данные), блок «Get and Transform». Скриншоты, как Power Query выглядит в различных версиях офиса приведены ниже:

../_images/PowerQueryInExcel2016.png

../_images/PowerQueryInExcel365on20180930.png

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • таблица - table

  • значение - value,

  • список - list,

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

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

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

../_images/ListOFQueriesExcel2016.png

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

../_images/PowerQueryShowQueries.png

../_images/PowerQueryListOfQueries.png

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

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

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

../_images/PQShowPaneProperties.png

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

../_images/PQname.png

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

../_images/referenceOtherQueries.png

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

#"имя запроса"

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

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

Команда Duplicate позволяет создать новый запрос и продублировать в нем все шаги исходного запроса (т.е. при дублировании появляется новый запрос с #"Имя (2)", в котором содержатся все шаги из исходного запроса). Новый запрос, созданный при использовании команды Duplicate, никак не связан с оригинальным запросом.

../_images/duplicate.png

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

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

../_images/referenceOperation.png

../_images/referenceOtherQueries-1.png

Шаг (Step)

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

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

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

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

../_images/stepsPQ.png

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

Чтобы посмотреть формулу конкретного шага необходимо включить отображение строки формул на вкладке view и выбрать интересующий шаг в панели «Applied Steps».

../_images/formulaBar.png

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

../_images/rightClickMoves.png

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

../_images/dragSteps.png

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

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

../_images/PQPBIinput.png

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

../_images/ribbonButtonsInput.png

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

../_images/PQInputData.png

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

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

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

../_images/DataTypesPowerQuery.png

  • 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

  • Table - таблица #table({"Заголовок1", "Заголовок2"}, { {"Строка1Столбец1", "Строка1Столбец2"}, {"Строка2Столбец1", "Строка2Столбец2"} })

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

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

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

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

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

../_images/IndicationOfDataTypesInPowerBI.png

  • При выбранном столбце, его тип данных в Power BI и Excel на вкладке «Home»

../_images/DataTypeInHomeTabPowerQuery.png

  • При выбранном столбце, его тип данных в Power BI и Excel на вкладке «Transform»

../_images/DataTypeInTransformTabPowerQuery.png

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

В Power Query есть функция «автоматически определять типы данных». При помощи данной функции Power Query будет подбирать тип данных к столбцу на основ первой тысячи строчек конкретного столбца.

../_images/autmaticDataType.pngавтоматически определять типы данных

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

Команда на Ribbon:

../_images/duplicateColumn.pngСоздание дубликата столбца

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

../_images/duplicateContext.pngСоздание дубликата столбца команда в контекстном меню

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

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

../_images/renameHeader.pngПереименование столбцов Power Query

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

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

Для этих целей отлично работает команда «remove other columns» (удалить прочие столбцы).

../_images/removeOtherColumns.pngRemove Other Columns - удаление прочих столбцов

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

Команду «разделить столбец по разделителю» можно найти в нескольких местах:

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

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

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

../_images/splitRightMenu.png

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

../_images/chooseOwnDelimeter.png

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

../_images/maxColumns.png

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

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

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

../_images/appendPQ.png

../_images/appendPQ2.png

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

../_images/appendExcel2016.png

../_images/apeend2.png

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

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

../_images/merge2.png

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

../_images/merge1.png

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

../_images/merge3.png

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

../_images/merge4.png

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

../_images/merge6.png

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

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

../_images/merge7.png

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

../_images/merge9.png

../_images/merge10.png

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

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

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

../_images/groupByButton.png

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

../_images/groupByRightClick.png

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

../_images/groupBy2.png

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

../_images/newColumn2.png

../_images/addNewColumn3.png

if then else условия

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

Пример:

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

../_images/ifthenelse1.png

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

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

../_images/ConditionalColumnManger1.png

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

../_images/conditional2.png

Для сведения - задаваемые условия в интерфейсе мастера пишут команду скрипта содержащего операторы if then else. Это значит, что первым будет проверено первое условие в мастере условного столбца. Все множество значений будет проверено по этому условию. В случае, если будут найдены результаты удовлетворяющие первому условию, то для них будет присвоено значение по результатам выполнения первого условия. Для остальных значений из множества, будет проверено следующее условие. И так далее, до тех пор, пока не будут проверенны все условия. Если для элементов множества не будет выполненно ни одно условие, то в условный столбец попадет значение из поля «В противном случае / (otherwise)».

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

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

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

  2. Выбрать пункт «Change type»

  3. И далее выбрать пункт «Using locale» ../_images/changeAsLocale.png

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

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

Команда «Remove Duplicates» проходит по выбранным столбцам (если выбрана вся таблица, то по всей таблице) и смотрит в них повторяющиеся ячейки (строчки, в случае если выбрана таблица). Если дубликаты найдены функция оставляет первую попавшуюся уникальную строчку и удаляет все последующие повторяющиеся Найти команду можно на Ribbon - Home - Remove Duplicates (Удалить дубликаты в выбранных столбцах)

../_images/removeDuplicates.png

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

../_images/removeDuplicates2.png

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

../_images/removeDuplicates3.png

Аналогичного результата можно добиться если использовать команду «Group By»

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

../_images/countRows2.png

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

Для выполнения необходимо щелкнуть правой кнопкой на конкретном шаге обработки. Выбрать пункт меню «Extract previous steps»

../_images/extract1.png

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

../_images/extract2.png

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

../_images/KeepToRows.png

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

../_images/KeepErrors.png

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

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

../_images/ReplaceErrors.png

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

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

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

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

../_images/PQWizardCSV2.png

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

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

../_images/folderFilesPreview.png

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

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

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

Текст взят из Power bi formula firewall privacy settings - marketing-wiki.ru

При работе в 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 пытается защитить нас, чтобы мы случайно не отправили какие-либо данные (вроде токена) на сервер-злоумышленника.

Однако, если мы работаем с API, то нам неминуемо нужно отправлять данные в интернет. Соответственно, чтобы не иметь проблем в этом процессе проще всего в настройках Power BI выключить Formula.Firewall. Это делается в разделе Privacy. Нужно выбрать 3-й пункт - «ignore privacy level settings»

../_images/IgnorePrivacyLevels.pngignore privacy level settings

Символы разрыва строки

При помощи операции найти или для разделения ячейки на отдельные строчки можно воспользоваться символами #(cr)#(lf) либо воспользоваться функцией Lines.FromText