7a. Power Query и язык М

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

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

Отмена свертывания столбцов – основной метод при работе с неудовлетворительно спроектированными таблицами.

Идентификация плохо спроектированных таблиц

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

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

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

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

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

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

Часто важное значение для анализа имеет контекстная информация, которая сохраняется вне таблиц. Объединение таблиц без контекста часто приводит к лишенным смысла результатам. Здесь будет показано, каким образом можно сохранять контекст таблицы и как объединять отдельные таблицы с их контекстной информацией.

Сохранение контекста в именах файлов и листах

Здесь рассматривается сценарий, когда контекстная информация доступна в виде наименования листа или имени файла, но не в виде столбца в таблице. Цель – включить эти данные в виде столбца для сохранения контекста. Допустим записи о товарах в категориях Bikes и Accessories расположены в двух отдельных книгах: C05E01 — Bikes.xlsx и C05E01 — Accessories.xlsx. Загрузите эти файлы на диск из приложенного архива.

Откройте новую книгу Excel. Загрузите файлы Bikes и Accessories, как отдельные запросы, выбрав вкладку Sheet1 в окне Навигатор. Запросы получат названия Sheet1 и Sheet1 (2). Информация, относящаяся к родительской категории, недоступна в виде столбца.

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

Подробнее »Сохранение контекста в Power Query

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

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

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

Рис. 1. Пример несоответствия имен столбцов

Подробнее »Объединение несовпадающих таблиц в Power Query

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

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

Добавление таблиц из папки

В компании AdventureWorks ежегодно по мере выпуска новых товаров создается новый файл. Сейчас имеется три файла: C03E03 - 2015.xlsx, C03E03 - 2016.xlsx, C03E03 - 2017.xlsx за три года. Но в будущем их может стать больше. Отчет должен включать все таблицы из папки. Скачайте приложенные файлы Excel и поместите их в выделенную папку. Откройте новую книгу в Excel. Пройдите Данные –> Получить данные –> Из файла –> Из папки. В окне Обзор кликните Открыть, хотя никакие файлы не выбраны:

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

Подробнее »Объединение большого числа таблиц в Power Query

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

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

Объединение двух таблиц

Мы снова воспользуемся демонстрационной базой MS, относящейся к вымышленной компании по производству велосипедов AdventureWorks. Загрузите файлы Excel с примерами с сайта или из приложенного архива. (Если вы хотите установить базу AdventureWorks см. Дик Куслейка. Визуализация данных при помощи дашбордов и отчетов в Excel, раздел Базы данных SOL Server. Для выполнения примеров установка базы не требуется.) Мы объединим сведения о велосипедах и аксессуарах к ним:

Рис. 1. Цель – из двух таблиц Bikes и Accessories создать одну

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

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

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

«Правильная» подготовка данных служит ключом к успеху их анализа. Если данные представлены в единой таблице, лучше разделить их на таблицу транзакций и одну или несколько справочных таблиц. Подробнее о пользе нескольких таблиц в модели данных см. Альберто Феррари, Марко Руссо. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel.

Рис. 1. Сохранить в таблице несколько столбцов

Подробнее »Разделение данных на таблицы поиска и фактов в 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