7a. Power Query и язык М

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

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

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

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

Подробнее »Работа с датами в Power Query

Ранее я перевел книгу Кен Пульс и Мигель Эскобар. Язык М для Power Query. А спустя некоторое время с удивлением обнаружил, что страничка книги самая посещаемая среди опубликованных за последние 5 лет. Механизм Power Query для Excel относительно новый, но весьма необычный. Это не чистая работа с данными в Excel, а инструмент импорта внешних данных и их предварительной обработки. Я постоянно извлекаю данные из Интернета, поэтому использую Power Query довольно часто. Гил Равив описывает многое из того, что есть у Кена Пульса, поэтому здесь я не повторяюсь. Больше внимания уделяю новым аспектам: языку М, анализу текстов и извлечению знания из текста. Книга содержим массу практически примеров, и будет очень полезна в освоении Power Query.

Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных. – СПб.: БХВ-Петербург, 2021. – 480 с.

Подробнее »Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных

Это окончание перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

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

При объединении двух таблиц может возникнуть ошибка, если данные поступают из разных источников – Formula.Firewall. В примере, который использовался в нескольких последних главах, вы сохраняли Excel-файл со сценарием в таком месте, что исходные файлы хранились в подкаталоге. Что может пойти не так? Изменение области видимости…[1]

Давайте поэкспериментируем, чтобы увидеть, как возникает ошибка. Откройте Query Organization.xlsx. Пройдите по меню Данные –> Получить данные –> Из файла –> Из книги. Перейдите к файлу Departments.xlsx. Выберите таблицу EmployeeDepts.

Рис. 25.1. Импорт таблицы EmployeeDepts

Подробнее »Глава 25. Организация запросов Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

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

С помощью Power Query довольно легко настроить полностью динамический календарь в Excel. Благодаря идеям главы 23 вы сможете управлять им на основе значений в двух ячейках Excel. Вам предстоит пройти четыре шага:

1. Добавить в Excel таблицу параметров для хранения границ календаря.
2. Подключить функцию fnGetParameter для передачи границ в Power Query.
3. Создать базовый столбец календаря с датами.
4. Добавить в календарь дополнительные столбцы (месяц, квартал, день недели, …).

Рис. 24.1. Таблица параметров

Подробнее »Глава 24. Динамический календарь в Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

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

В главе 21 мы рассмотрели, как пользовательские функции применяются для предварительной обработки наборов данных перед объединением файлов. В этой главе мы добавим «вишенку на торт». Вспомните пример из предыдущей главы, по извлечению расписаний из папки. Допустим, вы сохранили файл консолидации в H:\Payroll, и в течение нескольких месяцев хранили расписания во вложенной папке H:\Payroll\Timesheets. Если вы передадите свое решение кому-то другому, на новом компьютере название папки, скорее всего, будет иным. Нельзя ли настроить решение так, чтобы исходный путь не играл роли, а было лишь важно, что данные хранятся во вложенной папке …\Timesheets относительно файла консолидации?

Рис. 23.1. Таблица параметров

Подробнее »Глава 23. Таблицы динамических параметров в Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

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

В главе 18 вы узнали, как перенести в Power Query возможности функций Excel ЕСЛИ() и ЕСЛИОШИБКА(). Перенос других функций из библиотеки условной логики Excel сложнее. Он основан на использовании списков и пользовательских функций.

Рис. 22.1. Исходные данные

Подробнее »Глава 22. Продвинутая условная логика Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

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

Работа с пользовательской функцией, как правило, включает три этапа:

1. Один раз выполните сценарий обработки.
2. Преобразуйте сценарий в функцию.
3. Вызовите функцию из другого запроса.

Вернемся к сценарию из главы 18, где вы импортировали файл расписания 2015-03-14.txt. Там вы уже создали код для импорта и обработки. Теперь вы хотите применить ту же логику к подобным файлам. Однако вместо того, чтобы изменить Источник, вы хотите объединить все файлы (и любые другие, которые позже будут добавлены в папку). Для этого пригодится пользовательская функция.

Рис. 21.1. Ошибка из-за неверного пути к файлу; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Глава 21. Создание пользовательских функций Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

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

Откройте Understanding M.xlsx. Выберите любую ячейку в таблице Sales. Данные –> Из таблицы/диапазона. В редакторе Power Query удалите шаг Измененный тип.

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

Подробнее »Глава 20. Понимание языка М

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

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

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

Рис. 19.1. Единственная таблица в просматриваемой книге

Подробнее »Глава 19. Объекты Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

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

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

Допустим вы импортируете расписание из текстового файла:

Рис. 18.1. Текстовый файл содержит проблемы

Подробнее »Глава 18. Условная логика в Power Query