Перейти к содержимому

7a. Power Query и язык М

Язык М Power Query. Выражения и let

В Интернете довольно много примеров использования языка М Power Query для решения той или иной проблемы. Если можно просто скопировать и вставить код образца в ваш запрос, всё Ok. Но что делать, если требуется правка. Для этого нужно хоть немного разбираться в основах языка и его синтаксисе. Умники отсылают к официальной спецификации языка, но, поверьте, это не то, что нужно начинающим. Так что в этой и последующих заметках попробую объяснить азы доступным языком.[1]

Следующая заметка

Если вы впервые сталкиваетесь с Power Query, рекомендую начать с Марк Мур. Power Query.

Самая простая запись

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

Рис. 1. Самая простая запись на языке М

Подробнее »Язык М Power Query. Выражения и let

Николай Павлов. Скульптор данных в Excel с Power Query

Power Query – мощный инструмент для работы с данными в Microsoft Excel. В версии Excel 2010 года Power Query позиционировалась как надстройка. Сейчас – это элемент стандартного интерфейса вкладки Данные. И в самой ленте Excel не найти упоминания имени Power Query. Хотя за функционалом, связанным с импортом данных это имя сохранилось. С помощью Power Query можно легко решать множество задач, для которых раньше требовались сложные формулы или макросы. В книге подробно разбираются вопросы импорта данных в Excel из внешних источников (файлов разных форматов, баз данных, интернета и т.д.) и трансформации полученных таблиц. Книга рассчитана на средних и продвинутых пользователей. Ко всем описанным в книге задачам в комплекте идут файлы-примеры, которые можно использовать в работе.

Поскольку я уже прочитал по теме две книги (Кен Пульс и Мигель Эскобар. Язык М для Power Query и Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных), в настоящей заметке я рассказал на том, что было для меня новым (или что я уже успел забыть).

Николай Павлов. Скульптор данных в Excel с Power Query. – М.: Де’Либри, 2019. – 332 с.

Подробнее »Николай Павлов. Скульптор данных в Excel с Power Query

Заключительный проект в Power Query: объединяем все вместе

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию

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

Отчет о доходах компании Wide World Importers

Вы – специалист по данным в компании Wide World Importers. После атаки хакеров вас попросили восстановить отчет о доходах компании на основе старых данных. Ваш предшественник вел отчеты в трех файлах Excel за 2015-2017 годы. В каждом из них содержалось 12 листов – по одному на каждый месяц. А выручка 2018 года была экспортирована в формате csv. Нужно импортировать данные с 36 листов, объединить их с данными 2018 года и создать единый отчет.

Загрузите папку C14E01 и целевую таблицу в файле C14E01 — Goal.xlsx. Изучите файлы 2015.xlsx, 2016.xlsx и 2017.xlsx. Каждый лист содержит сводную таблицу формата 2*3:

Рис. 1. Исходные данные за 2015–2017 гг. в файлах Excel; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Заключительный проект в Power Query: объединяем все вместе

Основы текстовой аналитики в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

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

Поиск ключевых слов в текстовых столбцах

В следующем упражнении мы будем искать ключевые слова в сообщениях, которые Microsoft Press использует в постах на своей официальной странице в Facebook. Допустим, вам нужно оценить, какие из тем наиболее популярны в обсуждениях на Facebook: Microsoft Excel, Visual Studio, Azure, Windows.

Загрузите файл C11E01.xlsx и сохранить его в папке C:\Data\C11\. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C11E01.xslx и щелкните Импорт. В окне Навигатор выберите Sheet1 и щелкните Преобразовать данные. Обратите внимание, что посты Microsoft Press Facebook сохраняются в столбце Message. Ваша задача – выполнить поиск ключевых слов в этом столбце.

Рис. 1. Сообщения Microsoft Press на Facebook в редакторе Power Query; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Основы текстовой аналитики в Power Query

От ловушек к робастным запросам в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

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

Причины и следствия ловушек

В основе редактора Power Query лежат два основополагающих принципа:

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

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

Рис. 1. Жесткая ссылка на имена столбцов; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »От ловушек к робастным запросам в Power Query

Введение в язык формул M Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел 

Возможности языка M реализуются с помощью трех пользовательских интерфейсов:

  • Строка формул — дает возможность изменить функцию M для настройки шагов преобразований в соответствии с вашими потребностями. Обычно мы так поступали, если в пользовательском интерфейсе отсутствовала конкретная возможность (например, по применению расширенного логического условия при фильтрации столбца).
  • Окно Настраиваемый столбец — обеспечивает выполнение некоторых вычислений на уровне строк и сохранение результатов в новом столбце.
  • Расширенный редактор — позволяет изменить несколько шагов в запросе, включить код из другого запроса или создать пользовательскую функцию.

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

Можно выделить шесть этапов в изучении языка M. Каждый этап позволяет решать новые типы проблем с данными, но при этом он будет удерживать вас в определенной «зоне комфорта». Задайте себе вопрос: «Готовы ли вы приложить значительные усилия, чтобы перейти к следующему этапу?»

Рис. 1. Этапы зрелости в обучении языку М; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Введение в язык формул M Power Query

Совместная работа в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

Локальные файлы, параметры и шаблоны

Одна из наиболее распространенных проблем, с которыми сталкиваются при совместном написании отчета в Excel, связана с ситуацией, когда источники данных являются локальными файлами Excel или текстовыми файлами. Можно проиллюстрировать эту проблему очень распространенной историей двух авторов отчета: Алисой и Бобом.

Загрузите рабочую книгу Алисы, C08E01 — Alice.xlsx. В качестве источника данных она использовала файл C08E01.xlsx. Когда Боб получает отчет Алисы, при попытке обновления он получает ошибку:

Рис. 1. Ошибка, связанная с неверным путем к источнику данных; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Совместная работа в Power Query

Преобразование сведения в столбец в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

В предыдущих разделах вы познакомились с различными сценариями отмены свертывания столбцов, узнали, каким образом данное преобразование помогает трансформировать обобщенные (сводные) таблицы в таблицы фактов. Но иногда исходная таблица структурирована наоборот, слишком «плоско». Будто кто-то злоупотребил отменой свертывания столбцов и сформировал исходную таблицу с несколькими строками, по идее принадлежащими одному и тому же объекту. Здесь показано, как применить преобразование, обратное к отмене свертывания столбцов – сведение в столбец.

Загрузите файл C07E04.xlsx. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C07E04.xlsx и кликните Импорт. В окне Навигатор выберите Revenues, а затем щелкните Преобразовать данные. В окне редактора Power Query изучите таблицу, содержащую транзакции компании AdventureWorks. Она показана в верхней части рисунка:

Рис. 1. Исходная таблица и искаженная таблица после отмены свертывания столбцов; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Преобразование сведения в столбец в Power Query

Создание функции отмены свертывания столбцов в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

В предыдущем разделе был приведен универсальный алгоритм отмены свертывания столбцов таблицы с уровнями иерархии N*M. Отмена свертывания столбцов настолько полезна, что имеет смысл написать функцию. Такая функция позволит быстро обрабатывать исходные таблицы, берущие своё происхождение из сводных таблиц любой иерархии. С помощью такой функции ручное выполнение последовательности шагов объединяется в одно преобразование, которое будет пригодно для любой таблицы.

Рис. 1. Исходная таблица с иерархией 3х3

Подробнее »Создание функции отмены свертывания столбцов в Power Query

Отмена свертывания столбцов таблицы с несколькими уровнями иерархии в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

Ранее мы рассмотрели отмену свертывания столбцов для простой сводной таблицы и иерархии 2х2. Сейчас мы обобщим метод на сводную таблицу с любым числом уровней иерархии в строках и столбцах.

Загрузите файл C07E01.xlsx. Откройте его. Исходная таблица напоминает сводную таблицу с полями Country, State/Region и City, которые находятся в области строк, а поля Color, Parent Category и Category расположены в области столбцов. Поле Revenue находится в области значений.

Рис. 1. Таблица с исходными данными напоминает сводную таблицу в Excel; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Отмена свертывания столбцов таблицы с несколькими уровнями иерархии в Power Query