UPD: Обновил публикации в связи с выходом Power BI Desktop из стадии Preview 24 июля.

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

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

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


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

Выбор источника данных
Рисунок 2. Выбор источника данных

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

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

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

Окно редактора запросов
Рисунок 4. Окно редактора запросов

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

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

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

Применение фильтра
Рисунок 6. Применение фильтра

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

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

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

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

Окно настройки параметров разделения столбца
Рисунок 9. Окно настройки параметров разделения столбца

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

Окно настройки параметров разделения столбца
Рисунок 9. Окно настройки параметров разделения столбца

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

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

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

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

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

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

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

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

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

5. Дальше, мне нужно указать, что тип значений в столбцах «План» и «Факт» — десятичное число. Но перед этим мне нужно удалить из содержимого этих столбцов пробелы.
6. После выполнения операции проверяю столбцы на наличие ошибок и отрицательных значений. Поскольку в нашем случае наличие отрицательного значения означает ошибку ввода, то используя функцию преобразования значений выделяю абсолютное значение в столбцах «План» и «Факт». На этом базовая очистка данных закончена.
7. Для того, чтобы отображать показатели с привязкой по времени требуется указать дату для каждой записи. В таблице содержится месяц в текстовом виде и год. Для удобства примем, что данные отображаются на конец каждого месяца. Здесь нас подстерегает проблема – язык «М» не позволяет на текущий момент конвертировать названия месяцев в даты. Поэтому потребуется сделать несколько промежуточных шагов.
8. Создадим новый запрос, который будет содержать названия месяцев и их номера. Для этого создаем пустой запрос, открываем расширенный редактор и вставляем следующий код:
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. Добавление пустого запроса

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

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

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

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

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

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

Комментарии (2)


  1. Ananiev_Genrih
    24.07.2015 13:45

    Как раз сейчас бьюсь над тем что бы в графиках отражать сортировку месяцев не по алфавиту.
    Из доступного функционала есть сортировка с помощью другого поля (есть справочник вида Месяц->Индекс, присоединенный к основным данным), однако сослаться на индекс в графиках не получается- дает возможность при выборе поля сортировки ссылаться только на те объекты, которые принимает непосредственное участие в формировании графика. Есть какой то способ сортировать нормально? (текущая реализация через конкатенацию индекса префиксом — очень некрасивое решение...)


    1. dmitriysolovev Автор
      05.08.2015 12:51

      Добрый день! Как обещал в ЛС, отвечаю. К сожалению и в релизе ситуация с названиями месяцев, дней недели — не очень хорошая :(. Пока нашел следующие варианты:
      1. Использовать поле c типом «Дата» — корректно отображает месяц на графике, при условии что для всех значений каждого месяца установлена одна дата, например конец месяца. image
      2. Использовать сортировку по индексному полю. При этом определять такую сортировку нужно в модели данных. Затем индексное поле скрыть.
      3. Использовать таблицы дат с иерархиями. При настройке связи «Таблица данных» — «Таблица дат» значения дат в таблице данных нужно привести к типу «Дата и время». Время задать как 0:00:00, иначе связь не будет работать :(