Глава 2. Работа с Power Query/М

Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М Power Query. В главе 1 Знакомство с М мы рассказали о фундаментальных аспектах языка М. В разделе «Где используется M» мы кратко представили концепцию интерфейсов Power Query, включая Power Query Desktop и Power Query Online.

В этой главе содержатся дополнительные сведения о работе с M в интерфейсе Power Query в Power BI Desktop. Изложенные здесь знания позволят вам начать писать код M с помощью графического пользовательского интерфейса (GUI), предоставляемого редактором Power Query. Этот код может быть напрямую перенесен в другие интерфейсы, такие как Excel, Power Query Online или на сайт powerbi.com. Интерфейс Power Query Desktop позволяет сгенерировать код M без его написания. Код записывается пока вы кликаете на кнопки в интерфейсе редактора. Так что вы можете начать использовать M даже не зная языка.

Мои комментарии набраны с отступом.

Предыдущая глава       Содержание    Следующая глава

Скачать заметку в формате Word или pdf

Поскольку эта книга в основном посвящена языку M, в этой главе не дается исчерпывающего описания взаимодействия с Power Query. Тем не менее, понимание основ создания кода M очень важно, поэтому мы рассмотрим следующие темы:

• Обзор интерфейса Power Query Desktop
• Настройка кода, генерируемого во время работы с интерфейсом редактора
• Создание пользовательских столбцов
• Использование расширенного редактора

Для выполнения задач, описанных в этой главе, вам потребуется Power BI Desktop. Примеры можно загрузить с GitHub.

Подойдет и Excel, а вот файлы с примерами доступны только в формате *.pbix.

Обзор интерфейса Power Query Desktop

Power BI Desktop поставляется с подпрограммой, называемой редактором Power Query. Редактор Power Query это мощный инструмент для подготовки и преобразования данных, позволяющий очищать, формировать и объединять миллионы или даже миллиарды строк данных из разных источников, прежде чем использовать их в средствах аналитики, отчетности или визуализации. Очистка и преобразование данных играет центральную роль в создании надежной и хорошо структурированной семантической модели для эффективного анализа данных и создания отчетов.

Чтобы получить доступ к редактору Power Query, сначала необходимо скачать и установить Power BI Desktop. Power BI Desktop можно установить из Microsoft Store. Это рекомендуемый подход, хотя Power BI Desktop также можно установить по следующей ссылке.

Или русская версия.

Чтобы установить Power BI Desktop из Microsoft Store, выполните следующие действия:

• Откройте приложение Microsoft Store на ПК с Windows и найдите Power BI Desktop.
• Убедитесь, что приложение называется Power BI Desktop, а не Power BI. Это два разных приложения.
• Кликните Установить. Если Power BI Desktop уже установлен на ПК вместо кнопки Установить будет кнопка Открыть.
• После установки кнопка Установить изменится на Открыть.
• Нажмите кнопку Открыть, чтобы запустить Power BI Desktop.

Чтобы получить доступ к редактору Power Query из Power BI Desktop пройдите по меню Главная –> Запросы –> Преобразование данных.

Краткий обзор графического интерфейса редактора Power Query

Пользовательский интерфейс редактора Power Query состоит из семи основных областей.

Рис. 2.3. Редактор Power Query в Power BI Desktop

Заголовок (Title Bar) – маленькая полоска в верхней части окна редактора Power Query. Эта область является стандартной для приложений Windows. Щелчок левой кнопкой мыши по значку приложения в левом углу предоставляет стандартные команды изменения размера и общие команды выхода, такие как свернуть, развернуть и закрыть.

Рядом с этим значком находится панель быстрого доступа (Quick Access Toolbar). Эта панель может отображаться над лентой или под ней. Чтобы команду с ленты добавить на эту панель, кликните правой кнопкой мыши значок на ленте и выберите Добавить на панель быстрого доступа. По умолчанию отображается только значок Сохранить.

Справа от панели быстрого доступа находится имя открытого файла. В правой части строки находятся стандартные значки Свернуть, Развернуть/Восстановить и Закрыть.

Строка формул (Formula Bar) позволяет просматривать, вводить и изменять код M для одного шага запроса. Когда вы создаете запрос в редакторе Power Query с помощью графического интерфейса, на самом деле создается скрипт M, который выполняется для подключения, преобразования и импорта данных. Каждый из примененных шагов в запросе представляет собой строку кода языка M. Мы рассмотрим это чуть позже в этой главе.

Под строкой заголовка находится Лента (Ribbon), состоящая из семи основных вкладок и одной условной:

При нажатии на вкладке Файл (File) отображается всплывающее меню, которое позволяет сохранять файлы Power BI Desktop, а также закрывать редактор Power Query и применять изменения, внесенные в редакторе Power Query. Кроме того, меню Файл предоставляет доступ к Параметрам и Настройкам источника данных.

Главная (Home) содержит кнопки с наиболее распространенными операциями: подключение к источникам, управление параметрами и общие функции преобразования (удаление строк и столбцов, разделение столбцов и замена значений).

Преобразование (Transform) предоставляет функции манипулирования данными. Можно транспонировать строки и столбцы, поворачивать и отменять сведения столбцов, объединять столбцы, добавлять скрипты R и Python, а также выполнять научные, статистические, тригонометрические вычисления, вычисления даты и времени.

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

Представление (View) содержит элементы управления интерфейсом редактора Power Query. Например, отображением области Параметры запроса и Строки формул.

Инструменты (Tools) предоставляет доступ к средствам и параметрам диагностики.

Справка (Help) содержит полезные ссылки для получения справки по Power BI, в том числе ссылки на сайт сообщества Power BI, документацию, интерактивное обучение, видео и многое другое.

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

На Панели запросов (Queries Pane) отображается список запросов текущего файла Power BI. По мере создания запросов список будет расти. Если во время выполнения запроса возникают ошибки, в этой области отображаются наборы строк ошибок, созданных запросом.

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

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

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

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

Запросы можно включить или отключить для загрузки в конечном клиенте, например в Microsoft Excel или Power BI Desktop. При отключении загрузки запрос сохраняется в интерфейсе Power Query, но этот запрос не создает таблицу в клиентском приложении. Часто запросы, на которые ссылаются, отключаются от загрузки, так как такие запросы выполняют промежуточные шаги преобразования данных.

Рассмотрим практическое применение того, как можно использовать эти опции. Допустим, вы собираете данные о действиях из системы управления взаимоотношениями с клиентами (CRM), такой как Dynamics 365. Можно создать параметр, определяющий конечную точку системы исходных данных. Затем этот параметр будет использоваться в запросе Emails, который подключается к таблице Email для импорта данных. Затем вы выбираете столбцы Пользователь и Создано в и добавляете шаг для удаления всех остальных столбцов. Далее вы добавляете настраиваемый столбец Type, который просто возвращает Email для каждой строки.

Теперь, если вы хотите импортировать встречи, хорошим выбором будет дублировать запрос Emails и переименовать дубликат запроса в Appointments. Вы не копируете запрос, так как это создаст дубликат вашего параметра. Затем вы редактируете запрос, чтобы вернуть встречи для Type, а остальные преобразования данных остаются прежними.

Наконец, вы хотите получить финальную таблицу Emails and Appointments. Этот запрос добавляет (append) запросы Emails и Appointments. На эти базовые запросы ссылается новый запрос. Затем щелкните правой кнопкой мыши запросы Emails и Appointments и отключите загрузку, так как окончательная таблица содержит все строки из обоих базовых запросов.

Панель Параметры запроса (Query Setting Pane) предоставляет доступ к свойствам запроса, в первую очередь к имени запроса. По умолчанию имя запроса наследует имя таблицы в модели данных (semantic model). Под свойствами располагается список примененных шагов запроса. Запрос – это последовательность шагов или преобразований данных. При обработке данных для каждого преобразования создается шаг. Таким образом, выполнение запроса на обновление данных из источника сводится к повторному выполнению этих шагов или операций преобразования.

Панель Предварительный просмотр (Preview Pane) обеспечивает предварительный просмотр загружаемых и преобразуемых данных. Эта область является контекстной, отображая таблицу (или иное содержимое) для выбранного шага. Заголовки столбцов в области предварительного просмотра можно использовать для переименования столбцов, а также для доступа к широкому спектру операций преобразования и очистки.

Область Строки состояния (Status Bar) зависит от того, какой запрос выбран в редакторе Power Query. Здесь отображается полезная информация, такая как количество строк и столбцов в таблице, а также время последнего предварительного просмотра данных.

Ваш первый запрос

Чтобы создать свой первый запрос, вам понадобится файл Chapter 2 — Product Inventory.csv. В нем экспорт из хорошо известной и широко используемой базы данных AdventureWorks DW. Чтобы создать запрос, выполните следующие действия.

В Power BI Desktop создайте новый файл Chapter 2 — Working With Power Query M.pbix. Откройте  редактор Power Query, пройдя Главная –> Запросы –> Преобразование данных. В редакторе Power Query в меню Главная кликните Создать источник. Откроется окно Получить данные:

Рис. 2.5. Окно Получить данные редактора Power Query

Выберите Все –> Текстовый или CSV-файл, нажмите Подключить. Найдите файл Chapter 2 — Product Inventory.csv и нажмите кнопку Открыть. Отобразится окно загрузки:

Рис. 2.6. Предварительный просмотр данных

Нажмите Преобразовать данные. Данные будут загружены в редактор Power Query:

Рис. 2.7. Предварительный просмотр данных в редакторе Power Query

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

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

Строка состояния указывает, что имеется в общей сложности 8 столбцов и более 999 строк данных, и что профилирование столбцов (обсуждаемое ниже) основано на первых 1000 строк данных. Справа указано время загрузки предварительного просмотра.

В строке формул – фрагмент кода M, относящийся к последнему шагу запроса. Видно, что для изменения типов данных столбцов используется функция Table.TransformColumnTypes. Типы данных, выбранные для каждого столбца, отражаются в заголовках столбцов в области предварительного просмотра: целые числа имеют значок 123, даты – значок календаря, а валюта – значок $.

Автоматическое определение типа данных для столбца UnitCost не сработало, так как исходный файл содержал точку в качестве десятичного разделителя. Чтобы с этим примером можно было работать далее, нужно получить правильные значения. Для этого правой кнопкой мыши кликните на заголовок столбца UnitCost и пройдите Изменить тип –> Используя локаль. В окне Изменение типа по локали в поле Тип данных выберите Десятичное число с фиксированной запятой, а в поле Языковый стандарт – Английский (США). Подробнее об этом преобразовании будет рассказано позже.

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

Параметры и настройки источника данных

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

Параметры

Чтобы получить доступ к параметрам, управляющим внешним видом и поведением редактора Power Query, находясь в окне редактора, пройдите Файл –> Параметры и настройки –> Параметры. Откроется окно Параметры:

Рис. 2.9. Параметры редактора Power Query

Параметры в интерфейсе Power Query Desktop и в других средах (например, Excel), могут немного отличаться. Интерфейс Power Query Online, как правило, не предлагает параметры, так как Power Query Online предоставляется как служба.

Глобальные параметры влияют на все файлы. При этом некоторые параметры могут быть переопределены на уровне Текущего файла.

Глобальные параметры вкладки Загрузка данных

Определение типов отвечает за то, будут ли типы данных определяться автоматически для неструктурированных источников данных (например, текста или файлов Excel). Для структурированных источников данных типы данных наследуются от источника. По умолчанию автоматически определяются типы данных для столбцов и заголовки из первой строки (шаги 2 и 3 на рис. 2.7).

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

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

Параллельная загрузка таблиц. Для файлов, содержащих несколько запросов, Power BI загружает запросы параллельно для оптимизации производительности. Однако в некоторых ситуациях может потребоваться настроить эти параметры, в том числе Максимальное число одновременных оценок и Максимальный объем памяти, используемой для одного одновременной оценки (МБ). Дополнительные сведения об этих параметрах см. в документации Майкрософт Параметры конфигурации.

Логика операций со временем. По умолчанию Power BI Desktop автоматически создает скрытую таблицу дат для каждой даты или столбца даты и времени. Хотя это может быть удобно для обычного пользователя, большинство специалистов по работе с данными настоятельно рекомендуют отключить эту функцию. Активация этой функции может привести к раздуванию модели.

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

Вкладка Загрузка данных для текущего файла

Глобальные параметры загрузки могут быть переопределены для каждого файла на вкладке Параметры –> Текущий файл –> Загрузка данных:

Рис. 2.10. Параметры загрузки данных для текущего файла

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

Связи. Управляет импортом и автоматическим обнаружением связей между таблицами, загружаемыми запросом. Значения по умолчанию для этих параметров показаны на рисунке 2.10. Многие специалисты по работе с данными рекомендуют отключить автоматическое определение связей. Дополнительные сведения об этих параметрах см. в статье Создание связей и управление ими в Power BI Desktop.

Можно включить вопросы и ответы на естественном языке. Дополнительные сведения об этих параметрах см. в разделе Источники данных для вопросов и ответов на естественном языке.

Глобальные параметры редактора Power Query

Это параметры управляют внешним видом и поведением редактора Power Query:

Рис. 2.11. Параметры редактора Power Query в Power BI Desktop

Структура – показывает/скрывает область Параметры запроса и Строку формул в редакторе Power Query.

Импорт данных. При включении используется функция Web.BrowserContents при получении данных с веб-страниц. Эта функция может обнаруживать повторяющиеся шаблоны в содержимом, что превосходит простое обнаружение таблиц HTML.

Предварительный просмотр данных управляет внешним видом окна предварительного просмотра.

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

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

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

Рис. 2.12. Новый параметр – опция диалогового окна

Формула включает или отключает M IntelliSense (запатентованную технологию упреждающего ввода корпорации Майкрософт).

Параметры источника данных

Точно так же, как таблицы в модели данных поддерживаются запросами на языке M, большинство запросов поддерживаются источником данных. Эти определения источников данных хранятся в составе классического файла (Power BI Desktop или Excel).

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

После создания запроса к источнику данных, настройки можно увидеть и отредактировать, пройдя Файл –> Параметры и настройки –> Настройки источника данных

Рис. 2.13. Окно настроек источника данных

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

Для Источника данных в текущем файле доступны кнопки Изменить источник…, Экспорт: PBIDS, Править разрешения… и Очистить разрешения. Для параметра Глобальные разрешения отображаются три последние кнопки.

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

Рис. 2.14. Окно настроек источника данных CSV-файла

Кнопка Экспорт: PBIDS сохраняет источник данных в виде документа JSON.

Источник данных, созданный мною на рис. 2.7, при экспорте выглядит следующим образом:

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

На этом мы завершаем обзор интерфейса Power Query Desktop и переходим к редактированию кода M, созданного с помощью редактора Power Query.

Редактирование кода, созданного интерфейсом

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

Например, в области Примененные шаги выберите шаг Источник (см. рис. 2.7), а затем щелкнув стрелку вниз справа в строке формул, можно увидеть полный код языка M для этого шага:

Для подключения к файлу используются две вложенные функции: Csv.Document и File.Contents.

Функция File.Contents имеет один текстовый аргумент – путь к файлу + имя CSV-файла. Функция Csv.Document получает два параметра. Первый – результат File.Content, второй – запись из трех пар ключ/значение. Записи обсуждаются в главе 4 Общие сведения о значениях и выражениях.

Код языка M можно отредактировать в строке формул. Например, для CSV-файла из записи полезно удалить вторую пару «ключ-значение»:

Чтобы применить это изменение щелкните за пределами строки формул или нажмите Enter.

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

Можно внести еще одно изменение. Выберите шаг Измененный тип. В строке формул отобразится:

= Table.TransformColumnTypes(#»Повышенные заголовки»,{{«ProductKey», Int64.Type}, {«DateKey», Int64.Type}, {«MovementDate», type date}, {«UnitCost», type text}, {«UnitsIn», Int64.Type}, {«UnitsOut», Int64.Type}, {«UnitsBalance», Int64.Type}, {«EnglishProductName», type text}})

Вы узнаете больше об этой функции в последующих главах. Этот код преобразует тип данных для каждого столбца (числовой, текстовый, логический и т. д.). Столбец MovementDate имеет тип данных date. Можете добавить еще и время заменив на тип datetime. Получится:

= Table.TransformColumnTypes(#»Повышенные заголовки»,{{«ProductKey», Int64. Type}, {«DateKey», Int64.Type}, {«MovementDate», type datetime}, {«UnitCost», Currency.Type}, {«UnitsIn», Int64.Type}, {«UnitsOut», Int64.Type}, {«UnitsBalance», Int64.Type}, {«EnglishProductName», введите текст}})

Итак, строка формул – это первое, самое очевидное место, где можно написать код M. Далее мы рассмотрим еще один вариант ввода кода M – добавление пользовательских столбцов.

Создание пользовательских столбцов

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

Добавление столбца индекса

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

Чтобы добавить столбец индекса к запросу, созданному в разделе Первый запрос в редакторе Power Query перейдите на вкладку Добавление столбца, щелкните стрелку ниспадающего списка справа от опции Столбец индекса, выберите вариант От 1:

Рис. 2.15. Добавление столбца индекса, начинающегося с 1

При выполнении этого действия в области ПРИМЕНЕННЫЕ ШАГИ появится шаг Добавлен индекс. В строке формул отобразится код M:

Функция Table.AddIndexColumn используется для добавления столбца с именем Индекс (второй параметр) в таблицу, возвращаемую шагом Измененный тип (первый параметр). Этот столбец начинается с 1 (третий параметр), увеличивается на 1 (четвертый параметр) и имеет тип данных целое числе Int64.Type.

Добавление столбца из примеров

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

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

Рис. 2.16. Добавление столбца из примеров

Введите 166,25 в первых двух строках. Power Query автоматически определяет, что это число является умножением столбцов UnitsCost и UnitsBalance. Нажмите Ok.

Чтобы это сработало, надо преобразовать значения столбца UnitsCost, как указано в моем комментарии после рис. 2.7

Создается новый столбец с именем Умножение со следующей формулой:

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

Теперь перейдем к созданию/изменению столбцов с помощью математических операций.

Математические операции

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

Левой кнопкой мыши щелкните заголовок столбца UnitCost. Удерживая нажатой клавишу Ctrl на клавиатуре, выберите заголовок столбца UnitsBalance. Перейдите на вкладку Добавление столбца, кликните кнопку на ленте Стандартный –> Умножить.

Рис. 2.17. Умножение двух столбцов

Выполнение этих действий добавляет шаг Вставлено: умножение. В строке формул отображается:

Здесь функция Table.AddColumn используется для добавления столбца с именем Умножение (второй параметр) в таблицу, возвращаемую шагом #»Добавлен индекс» (первый параметр), где для каждой строки столбец UnitCost умножается на столбец UnitsBalance (третий параметр), столбцу Умножение назначается тип данных Currency.Type (четвертый параметр). Не волнуйтесь, если какой-то синтаксис, например использование ключевого слова each, прямо сейчас вам непонятен. Синтаксис будет объяснен в следующих главах.

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

Добавление пользовательских столбцов с помощью кода М

Перейдите на вкладку Добавление столбца, кликните Настраиваемый столбец:

Рис. 2.18. Диалоговое окно Настраиваемый столбец

В окне Настраиваемый столбец в поле Имя нового столбца замените Пользовательский на MovementBalance. В области Настраиваемая формула столбца после знака = введите код:

([UnitsIn]-[UnitsOut])*[UnitCost]

Нажмите Ok.

Для каждой строки данных столбец UnitsOut вычитается из столбца UnitsIn, а затем результат умножается на значение из столбца UnitCost. Результирующий столбец называется MovementBalance и содержит положительную или отрицательную стоимость разницы в перемещенных единицах в каждой строке.

Выполнение этих действий создаст шаг Добавлен пользовательский объект в области Примененные шаги. В строке формул отображается код M:

Код 2.4 похож на код 2.3, но отсутствует четвертый параметр, указывающий тип данных для нового столбца. Взглянув на заголовок столбца MovementBalance мы увидим, что значок типа данных – ABC123 – указывает на то, что тип данных для столбца не определен. Чтобы изменить код перейдите в строку формул и добавьте четвертый аргумент Currency.Type:

Значок типа данных изменится на $.

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

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

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

Чтобы получить доступ к расширенному редактору, пройдите Главная –> Запрос –> Расширенный редактор. Откроется окно Расширенный редактор:

Рис. 2.20. Расширенный редактор

Здесь показан код M запроса Chapter 2 — Product Inventory. Все шаги запроса содержатся в выражении let. Я раскрыл список Отобразить параметры, чтобы Включить перенос по словам и Отображение номеров строк.

Щелчок по знаку вопроса (?) открывает веб-браузер на странице Справочник по функции Power Query M от Microsoft. Отличным источником, который, возможно, предоставляет еще больше информации, является сайт powerquery.how, поддерживаемый Риком де Гроотом. Например, powerquery.how содержит обширный список перечислений кода M (определенных, фиксированных наборов возможных значений для типов), в то время как такая информация почти полностью отсутствует в официальной документации Microsoft по языку M.

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

Для изменения масштаба в расширенном редакторе доступны горячие клавиши:

• Увеличение: Ctrl + Shift + =
• Уменьшение: Ctrl + Shift + —

Эти горячие клавиши также работают в самом редакторе Power Query.

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

Вот что пояснил Chat GPT. Верблюжий регистр или камелКейс – термин описывающий стиль написания идентификаторов (названий переменных или функций), в котором все слова пишутся слитно, и каждое слово начинается с прописной буквы, за исключением первого слова, которое начинается со строчной буквы. В результате слова выглядят как горбы верблюда, отсюда и название camelCase.

Как видно на рис. 2.20 имена шагов, включающие пробелы, должны начинаться с символа решетки (#), а само имя заключаться в двойные кавычки.

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

Рис. 2.21. Расширенный редактор с пользовательскими изменениями кода

Благодаря этим изменениям код становится намного чище и легче для чтения.

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

После того, как вы закончите редактировать код M в расширенном редакторе, вы можете применить изменения, нажав кнопку Готово. Чтобы загрузить данные в модель Power BI или Excel, нажмите кнопку Закрыть и загрузить в левом углу вкладки Главная.

На этом мы завершаем изучение расширенного редактора Power Query Desktop.

Саммари

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

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

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