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

Февраль 2022

Основы текстовой аналитики в 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

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

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

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

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

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

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

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

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

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

Сохранение контекста в 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

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

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

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

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

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