7a. Power Query и язык М

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Подробнее »Отмена свертывания столбцов таблицы с несколькими уровнями иерархии в 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