+7 (812) 670-9095
Обратная связьEnglish
Главная → Статьи → Power BI → Простой план-фактный анализ в Power BI Desktop. Часть первая – консолидация и очистка данных
Полезный совет
Как быстро и легко сделать вкусный отчет из сложной таблицы в Excel? Ингредиенты: сводная таблица, пара минут времени.Подробнее
Версия для печати

Простой план-фактный анализ в Power BI Desktop. Часть первая – консолидация и очистка данных

15 июля 2015

Сегодня хочу рассказать про построение простого дашборда с план-фактным анализом доходов и расходов в Power BI Desktop и Power BI. В первой части рассмотрим работу с запросами, научимся объединять данные из нескольких источников и выполнять их очистку. 

Исходные данные: несколько файлов Excel в которых хранится информация о показателях доходов и расходов компании за некоторое время. Поскольку форма отчетов и статьи учета несколько раз менялись, то листы с информацией имеют похожую, но не совсем одинаковую структуру. На выходе требуется получить нечто, что позволит руководителям получить представление о том, что происходит с финансами компании. 

Давайте посмотрим, как Power BI Desktop позволит объединить данные и их визуализировать.

Стартовое окно Power BI Desktop | АстроСофт

Рисунок 1. Стартовое окно Power BI Designer

При запуске Power BI Desktop просит указать источник данных или выбрать один из недавних источников. Кстати, по поводу источников – возможно подключение к большому их количеству, включая онлайн и локальные источники. Например, Google Analytics. В нашем случае все отчеты хранятся в одной папке, поэтому выберем в качестве источника «Папка». Такой выбор позволит в дальнейшем добавлять новые отчеты в эту папку и подгружать их данные для анализа нажатием кнопки «Обновить». 
Выбор источника данных
Рисунок 2. Выбор источника данных


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

Предварительный просмотр результатов запроса
Рисунок 3. Предварительный просмотр результатов запроса


После нажатия кнопки «Изменить» автоматически открывается окно «Редактор запросов», в котором можно и нужно выполнить очистку и подготовку данных.

Запрос к папке в Power BI Designer | АстроСофт
Рисунок 4. Окно редактора запросов


Как видно на снимке экрана, Power BI Desktop подгрузил файлы из папки и основную метаинформацию. Удалим все столбцы, кроме «Content», «Name» и «Extension». Поскольку имя файла показывает, к какому году относится соответствующий отчёт, мы его для этого и используем.

Если нажать на любую строку в столбце «Content», Power BI Desktop откроет содержимое соответствующей книги. Обратите внимание, что в разделе «Параметры запроса» все выполненные шаги записываются и их можно изменять или удалять. Если же открыть окно «Расширенный редактор», то откроется окно, в котором будет виден программный код для всех выполненных действий. Да в Power BI Desktop есть свой язык программирования “M” и это очень круто.

Расширенный редактор запросов
Рисунок 5. Расширенный редактор запросов


Поскольку меня интересует содержимое всех файлов Excel в папке, а не только одного, то я удалю два последних шага и использую некоторые функции языка “M” для парсинга содержимого книг Excel из папки. Для этого я добавлю новый столбец, используя функцию Excel.Workbook, которая позволяет «извлекать» содержимое книг Excel. Новый столбец содержит в себе значения типа «Таблица», что позволяет мне «развернуть» его содержимое на несколько других столбцов.

Применение фильтра
Рисунок 6. Добавление пользовательского столбца


Теперь нужно выполнить «извлечение» содержимого книг Excel. Для этого я добавлю новый столбец, используя функцию Excel.Workbook, которая позволяет «извлекать» содержимое книг Excel. Новый столбец содержит в себе значения типа «Таблица», что позволяет «развернуть» его содержимое на несколько других столбцов. При «развертывании» можно выбирать, какие столбцы будут отображены. В данном случае смысловую нагрузку несут столбцы «Data» и «Item»

Добавление пользовательского столбца
Рисунок 7. Добавление пользовательского столбца


Столбец «Data» содержит в себе данные листов Excel, а «Name» и «Item» я в дальнейшем использую для временных отметок. Поскольку столбец «Name» содержит данные вида yyyy.xlsx, где yyyy это год отчета, то выполним простую операцию разделения данных в столбце. Разделение можно выполнять как по количеству символов, так и по разделителю. В данному случае столбец нужно делить по разделителю.

Разделение столбца
Рисунок 8. Разделение столбца


  1. Использую верхние строки как заголовки и затем переименую столбцы. Удалю столбцы «Фактическое отклонение» и «Отклонение в %». В дальнейшем их пересчитаем.
  2. Удалю строки, которые содержат пустые значения и значение «Показатель» в столбце «Показатель», применив фильтрацию. Таким же образом удалю строки, в которых содержатся суммарные значения, например «Итого ЧОД», «Итого» и т.д.

Меню фильтрации данных | AstroSoft
Рисунок 9. Меню фильтрации данных

После разделения столбца нужно будет его переименовать.


Подготовленный к «развертыванию» запрос
Рисунок 10. Подготовленный к «развертыванию» запрос

Затем я «развертываю» столбец Data и вижу содержимое всех файлов и листов Excel, при этом в виде, который непригоден для построения итоговой отчетности. Но я могу использовать возможности Power BI Desktop для очистки данных.

Запрос после «развертывания» содержимого файлов
Рисунок 11. Запрос после «развертывания» содержимого файлов


  1. Использую верхние строки как заголовки и затем переименую столбцы. Удалю столбцы «Фактическое отклонение» и «Отклонение в %». В дальнейшем их пересчитаем.
  2. Удалю строки, которые содержат пустые значения и значение «Показатель» в столбце «Показатель», применив фильтрацию. Таким же образом удалю строки, в которых содержатся суммарные значения, например «Итого ЧОД», «Итого» и т.д.

Меню фильтрации данных 
Рисунок 12. Меню фильтрации данных

  1. Используя функцию «Замена значений» выполню замену синонимов, например «Доход» и «Доходы».

Замена значений
Рисунок 13. Замена значений

  1. Еще немного изучив содержимое столбца «Показатель» обнаруживаю, что все доходы у меня относятся к показателю «Доход» или «Доходы». Всё остальное относится к расходам, что сильно облегчает задачу. Для удобства дальнейшей обработки и фильтрации создам столбец «Категория», который будет содержать значение «Доход», если в столбце «Показатель» присутствует слово «Доход», а во всех остальных случаях примет значение «Расход».

Добавление столбца «Категория»
Рисунок 14. Добавление столбца «Категория»

Дальше, мне нужно указать, что тип значений в столбцах «План» и «Факт» — десятичное число. Но перед этим мне нужно удалить из содержимого этих столбцов пробелы. 

  1. После выполнения операции проверяю столбцы на наличие ошибок и отрицательных значений. Поскольку в нашем случае наличие отрицательного значения означает ошибку ввода, то используя функцию преобразования значений выделяю абсолютное значение в столбцах «План» и «Факт». На этом базовая очистка данных закончена.
  2. Для того, чтобы отображать показатели с привязкой по времени требуется указать дату для каждой записи. В таблице содержится месяц в текстовом виде и год. Для удобства примем, что данные отображаются на конец каждого месяца. Здесь нас подстерегает проблема – язык «М» не позволяет на текущий момент конвертировать названия месяцев в даты. Поэтому потребуется сделать несколько промежуточных шагов.
  3. Создадим новый запрос, который будет содержать названия месяцев и их номера. Для этого создаем пустой запрос, открываем расширенный редактор и вставляем следующий код: 

    let Source = {"январь", "февраль", "март", "апрель", "май", "июнь", "июль", "август", "сентябрь", "октябрь", "ноябрь", "декабрь"}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1), #"Added to Column" = Table.TransformColumns(#"Added Index", {{"Index", each List.Sum({_, 1})}}), #"Renamed Columns" = Table.RenameColumns(#"Added to Column",{{"Column1", "Месяц"}}) in #"Renamed Columns"
Добавление пустого запроса
Рисунок 15. Создание пустого запроса

  1. Перехожу в запрос «План-Факт» и объединяю запросы, выбрав нужный тип объединения.

Объединение запросов
Рисунок 16. Объединение запросов

  1. Данные из запроса «Месяцы» добавились как новый столбец. Раскрываю его и теперь у нас есть все данные для формирования даты. Создаю новый столбец с названием «Дата», используя формулу: =Date.EndOfMonth(#date([Год],[Index],1)) Столбец добавлен и он содержит последнее число каждого месяца. Для того, чтобы Power BI Desktop мог группировать даты по месяцам и годам, нужно будет явно задать тип «Date»

Добавление столбца «Дата»
Рисунок 17. Добавление столбца «Дата»

  1. Удалим столбцы Index, Год и Месяц. Они нам больше не нужны. Затем нужно нажать на кнопку «Закрыть и загрузить», чтобы перейти к моделированию и визуализации данных.

На этом базовая часть обработки данных закончена и можно перейти к визуализации. Возможности визуализации данных в Power BI Desktop рассмотрим в следующей части.

Источник:http://habrahabr.ru/post/262655/