Всем привет! Сегодня хочу рассказать про построение простого дашборда с план-фактным анализом доходов и расходов в Power BI Designer Preview и Power BI. В первой части рассмотрим работу с запросами, научимся объединять данные из нескольких источников и выполнять их очистку.
Исходные данные: несколько файлов Excel в которых хранится информация о показателях доходов и расходов компании за некоторое время. Поскольку форма отчетов и статьи учета несколько раз менялись, то листы с информацией имеют похожую, но не совсем одинаковую структуру. На выходе требуется получить нечто, что позволит руководителям получить представление о том, что происходит с финансами компании
Давайте посмотрим, как Power BI Designer Preview (в ближайшем будущем Power BI Desktop) позволит объединить данные и их визуализировать.
При запуске Power BI Designer просит указать источник данных или выбрать один из недавних источников. Кстати, по поводу источников – возможно подключение к большому их количеству, включая онлайн и локальные источники. Например, Google Analytics. В нашем случае все отчеты хранятся в одной папке, поэтому выберем в качестве источника «Folder». Такой выбор позволит в дальнейшем добавлять новые отчеты в эту папку и подгружать их данные для анализа нажатием кнопки «Обновить».
Рисунок 1. Стартовое окно Power BI Designer
Рисунок 2. Выбор источника данных
В Power BI Designer существует два основных режима работы – Report (предназначен для разработки визуальных отчетов) и Query (в этом режиме выполняется очистка и подготовка данных для использования в отчетах). По умолчанию открывается режим Report, этому требуется перейти в режим Query.
Рисунок 3. Окно Power BI Designer
Как видно на снимке экрана, Power BI Designer подгрузил файлы из папки и основную метаинформацию. Удалим все столбцы, кроме Content и Имя файла. Поскольку имя файла показывает, к какому году относится соответствующий отчёт, мы его для этого и используем.
Рисунок 4. Запрос к папке в Power BI Designer
Если я нажму на любую строку в столбце «Content», PowerBI Designer откроет мне содержимое соответствующей книги. Обратите внимание, что в разделе Query Settings все выполненные мной шаги записываются и я, при необходимости, могу их изменить или удалить. А если я перейду на вкладку View и кликну на Advanced Editor, то откроется окно, в котором будет виден программный код для всех выполненных мной действий. Да, Power BI Designer использует язык программирования “M”, точно такой же как и Power Query для Excel.
Рисунок 5. Расширенный редактор запросов
Поскольку меня интересует содержимое всех файлов Excel в папке, а не только одного, то я удалю два последних шага и использую некоторые функции языка “M” для парсинга содержимого книг Excel из папки.
Для этого я добавлю новый столбец, используя функцию Excel.Workbook, которая позволяет «извлекать» содержимое книг Excel. Новый столбец содержит в себе значения типа «Таблица», что позволяет мне «развернуть» его содержимое на несколько других столбцов.
Рисунок 6. Добавление пользовательского столбца
Поскольку большинство «развернутых» столбцов не несет в данном случае смысловой нагрузки, то я удалю все, кроме «Name», «Data» и «Item». Столбец «Data» содержит в себе данные листов Excel, а «Name» и «Item» я в дальнейшем использую для временных отметок. После переименования и изменения порядка столбцов таблица принимает вид, показанный на рисунке
Рисунок 7. Подготовленный к «развертыванию» запрос
Затем я «развертываю» столбец Data и вижу содержимое всех файлов и листов Excel, при этом в виде, который непригоден для построения итоговой отчетности. Но я могу использовать возможности Power BI Designer Preview для очистки данных.
Рисунок 8. Запрос после «развертывания» содержимого файлов
1. Использую верхние строки как заголовки и затем переименую столбцы. Удалю столбцы «Фактическое отклонение» и «Отклонение в %». В дальнейшем их пересчитаем.
2. Удалю строки, которые содержат пустые значения и значение «Показатель» в столбце «Показатель», применив фильтрацию. Таким же образом удалю строки, в которых содержатся суммарные значения, например «Итого ЧОД», «Итого» и т.д.
Рисунок 9. Меню фильтрации данных
3. Используя функцию «Replace Values» выполню замену синонимов, например «Доход» и «Доходы».
Рисунок 10. Замена значений
4. Еще немного изучив содержимое столбца «Показатель» обнаруживаю, что все доходы у меня относятся к показателю «Доход» или «Доходы». Всё остальное относится к расходам, что сильно облегчает задачу. Для удобства дальнейшей обработки и фильтрации создам столбец «Категория», который будет содержать значение «Доход», если в столбце «Показатель» присутствует слово «Доход», а во всех остальных случаях примет значение «Расход».
Рисунок 11. Добавление столбца «Категория»
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" 
Рисунок 12. Добавление пустого запроса
9. Перехожу в запрос «План-Факт» и объединяю запросы.
Рисунок 13. Объединение запросов
10. Данные из запроса «Месяцы» добавились как новый столбец. Раскрываю его и теперь у нас есть все данные для формирования даты. Создаю новый столбец с названием «Дата», используя формулу: =Date.EndOfMonth(#date([Год],[Index],1)) Столбец добавлен и он содержит последнее число каждого месяца. Для того, чтобы Power BI Designer мог группировать даты по месяцам и годам, нужно будет явно задать тип «Date»
Рисунок 14. Добавление столбца «Дата»
Рисунок 15. Столбец «Дата» добавлен
11. Удалим столбцы Index, Год и Месяц. Они нам больше не нужны.
На этом базовая часть обработки данных закончена и можно перейти к визуализации. Возможности визуализации данных в Power BI Designer рассмотрим в следующей части.
ссылка на оригинал статьи http://habrahabr.ru/post/262655/
Добавить комментарий