Часть первая – консолидация и очистка данных из песочницы tutorial
UPD: Обновил публикации в связи с выходом Power BI Desktop из стадии Preview 24 июля.
Сегодня хочу рассказать про построение простого дашборда с план-фактным анализом доходов и расходов в Power BI Desktop и Power BI. В первой части рассмотрим работу с запросами, научимся объединять данные из нескольких источников и выполнять их очистку.
Исходные данные: несколько файлов Excel в которых хранится информация о показателях доходов и расходов компании за некоторое время. Поскольку форма отчетов и статьи учета несколько раз менялись, то листы с информацией имеют похожую, но не совсем одинаковую структуру. На выходе требуется получить нечто, что позволит руководителям получить представление о том, что происходит с финансами компании
Давайте посмотрим, как Power BI Desktop позволит объединить данные и их визуализировать.
![Стартовое окно Power BI Desktop](https://habrastorage.org/files/74b/302/516/74b3025165dc49f9aa44a9f51b93c9ed.png)
Рисунок 1. Стартовое окно Power BI Desktop
При запуске Power BI Desktop просит указать источник данных или выбрать один из недавних источников. Кстати, по поводу источников – возможно подключение к большому их количеству, включая онлайн и локальные источники. Например, Google Analytics. В нашем случае все отчеты хранятся в одной папке, поэтому выберем в качестве источника «Папка». Такой выбор позволит в дальнейшем добавлять новые отчеты в эту папку и подгружать их данные для анализа нажатием кнопки «Обновить».
![Выбор источника данных](https://habrastorage.org/files/aa5/275/beb/aa5275beb79c46bc9cf8e7d5eb78d0af.png)
Рисунок 2. Выбор источника данных
После нажатия кнопки «Подключиться» отображается окно предварительного просмотра результатов запроса к источнику данных. Можно загрузить данные «как есть» или перейти в режим редактирования запроса, нажав на кнопку «Изменить». В большинстве случаев результаты запроса требуется обрабатывать.
![Предварительный просмотр результатов запроса](https://habrastorage.org/files/6a5/124/1d0/6a51241d069b4913a2adee45075663ba.png)
Рисунок 3. Предварительный просмотр результатов запроса
После нажатия кнопки «Изменить» автоматически открывается окно «Редактор запросов», в котором можно и нужно выполнить очистку и подготовку данных.
![Окно редактора запросов]()
Рисунок 4. Окно редактора запросов
Как видно на снимке экрана, Power BI Desktop подгрузил файлы из папки и основную метаинформацию. Удалим все столбцы, кроме «Content», «Name» и «Extension». Поскольку имя файла показывает, к какому году относится соответствующий отчёт, мы его для этого и используем.
Если нажать на любую строку в столбце «Content», Power BI Desktop откроет содержимое соответствующей книги. Обратите внимание, что в разделе «Параметры запроса» все выполненные шаги записываются и их можно изменять или удалять. Если же открыть окно «Расширенный редактор», то откроется окно, в котором будет виден программный код для всех выполненных действий. Да в Power BI Desktop есть свой язык программирования “M” и это очень круто.
![Расширенный редактор запросов](https://habrastorage.org/files/44f/e8a/71f/44fe8a71ffc6479eab90a6d7291e0fd0.png)
Рисунок 5. Расширенный редактор запросов
Поскольку для формирование отчета требуется содержимое всех файлов Excel в папке, а не только одного, то я удалю два последних шага и использую некоторые функции языка “M” для парсинга содержимого книг Excel из папки.
Перед дальнейшей обработкой данных также нужно учесть то, что в папку могут быть подгружены не только файлы Excel. Поэтому нужно применить фильтр к столбцу «Extension», что позволит исключить ненужные типы файлов.
![Применение фильтра](https://habrastorage.org/files/f71/900/485/f7190048574d49ed82ba9d63c0c887d6.png)
Рисунок 6. Применение фильтра
Теперь нужно выполнить «извлечение» содержимого книг Excel. Для этого я добавлю новый столбец, используя функцию Excel.Workbook, которая позволяет «извлекать» содержимое книг Excel. Новый столбец содержит в себе значения типа «Таблица», что позволяет «развернуть» его содержимое на несколько других столбцов. При «развертывании» можно выбирать, какие столбцы будут отображены. В данном случае смысловую нагрузку несут столбцы «Data» и «Item»
Рисунок 7. Добавление пользовательского столбца
Столбец «Data» содержит в себе данные листов Excel, а «Name» и «Item» я в дальнейшем использую для временных отметок.
Поскольку столбец «Name» содержит данные вида yyyy.xlsx, где yyyy это год отчета, то выполним простую операцию разделения данных в столбце. Разделение можно выполнять как по количеству символов, так и по разделителю. В данному случае столбец нужно делить по разделителю.
![Разделение столбца](https://habrastorage.org/files/996/a61/66a/996a6166a42d45a394e5fa691e81e980.png)
Рисунок 8. Разделение столбца
![Окно настройки параметров разделения столбца](https://habrastorage.org/files/32f/ddf/b4e/32fddfb4e62a4925bfab786ae55bb79e.png)
Рисунок 9. Окно настройки параметров разделения столбца
После разделения столбца нужно будет его переименовать.
![Окно настройки параметров разделения столбца](https://habrastorage.org/files/32f/ddf/b4e/32fddfb4e62a4925bfab786ae55bb79e.png)
Рисунок 9. Окно настройки параметров разделения столбца
![Подготовленный к развертыванию запрос](https://habrastorage.org/files/d6e/5ce/680/d6e5ce680124424391df898627fc5b03.png)
Рисунок 10. Подготовленный к «развертыванию» запрос
Затем я «развертываю» столбец Data и вижу содержимое всех файлов и листов Excel, при этом в виде, который непригоден для построения итоговой отчетности. Но я могу использовать возможности Power BI Desktop для очистки данных.
![Запрос после развертывания содержимого файлов](https://habrastorage.org/files/151/bf9/281/151bf9281a804d3ab09e2d27c6ff5fd7.png)
Рисунок 11. Запрос после «развертывания» содержимого файлов
1. Использую верхние строки как заголовки и затем переименую столбцы. Удалю столбцы «Фактическое отклонение» и «Отклонение в %». В дальнейшем их пересчитаем.
2. Удалю строки, которые содержат пустые значения и значение «Показатель» в столбце «Показатель», применив фильтрацию. Таким же образом удалю строки, в которых содержатся суммарные значения, например «Итого ЧОД», «Итого» и т.д.
![Меню фильтрации данных](https://habrastorage.org/files/1fb/94a/278/1fb94a278c8f48f69bc5f27df3838cff.png)
Рисунок 12. Меню фильтрации данных
3. Используя функцию «Замена значений» выполню замену синонимов, например «Доход» и «Доходы».
![Замена значений](https://habrastorage.org/files/2bd/26b/9a7/2bd26b9a79d9486bbc6b3c81f10dbd98.png)
Рисунок 13. Замена значений
4. Еще немного изучив содержимое столбца «Показатель» обнаруживаю, что все доходы у меня относятся к показателю «Доход» или «Доходы». Всё остальное относится к расходам, что сильно облегчает задачу. Для удобства дальнейшей обработки и фильтрации создам столбец «Категория», который будет содержать значение «Доход», если в столбце «Показатель» присутствует слово «Доход», а во всех остальных случаях примет значение «Расход».
![Добавление столбца Категория](https://habrastorage.org/files/37b/5c7/86d/37b5c786d42340ada719109d2f446018.png)
Рисунок 14. Добавление столбца «Категория»
5. Дальше, мне нужно указать, что тип значений в столбцах «План» и «Факт» — десятичное число. Но перед этим мне нужно удалить из содержимого этих столбцов пробелы.
6. После выполнения операции проверяю столбцы на наличие ошибок и отрицательных значений. Поскольку в нашем случае наличие отрицательного значения означает ошибку ввода, то используя функцию преобразования значений выделяю абсолютное значение в столбцах «План» и «Факт». На этом базовая очистка данных закончена.
7. Для того, чтобы отображать показатели с привязкой по времени требуется указать дату для каждой записи. В таблице содержится месяц в текстовом виде и год. Для удобства примем, что данные отображаются на конец каждого месяца. Здесь нас подстерегает проблема – язык «М» не позволяет на текущий момент конвертировать названия месяцев в даты. Поэтому потребуется сделать несколько промежуточных шагов.
8. Создадим новый запрос, который будет содержать названия месяцев и их номера. Для этого создаем пустой запрос, открываем расширенный редактор и вставляем следующий код:
![Добавление пустого запроса](https://habrastorage.org/files/775/063/35e/77506335ed5f4f79889f59e35b3b34f7.png)
Рисунок 15. Добавление пустого запроса
9. Перехожу в запрос «План-Факт» и объединяю запросы, выбрав нужный тип объединения.
![Объединение запросов](https://habrastorage.org/files/4b9/222/ec2/4b9222ec284c4b719597824abd49cb6e.png)
Рисунок 16. Объединение запросов
10. Данные из запроса «Месяцы» добавились как новый столбец. Раскрываю его и теперь у нас есть все данные для формирования даты. Создаю новый столбец с названием «Дата», используя формулу: =Date.EndOfMonth(#date([Год],[Index],1)) Столбец добавлен и он содержит последнее число каждого месяца. Для того, чтобы Power BI Desktop мог группировать даты по месяцам и годам, нужно будет явно задать тип «Date»
![Добавление столбца Дата](https://habrastorage.org/files/5a3/0b3/839/5a30b3839ff247aca49f2a5b37f7e213.png)
Рисунок 17. Добавление столбца «Дата»
11. Удалим столбцы Index, Год и Месяц. Они нам больше не нужны. Затем нужно нажать на кнопку «Закрыть и загрузить», чтобы перейти к моделированию и визуализации данных.
На этом базовая часть обработки данных закончена и можно перейти к визуализации. Возможности визуализации данных в Power BI Desktop рассмотрим в следующей части.
Сегодня хочу рассказать про построение простого дашборда с план-фактным анализом доходов и расходов в Power BI Desktop и Power BI. В первой части рассмотрим работу с запросами, научимся объединять данные из нескольких источников и выполнять их очистку.
Исходные данные: несколько файлов Excel в которых хранится информация о показателях доходов и расходов компании за некоторое время. Поскольку форма отчетов и статьи учета несколько раз менялись, то листы с информацией имеют похожую, но не совсем одинаковую структуру. На выходе требуется получить нечто, что позволит руководителям получить представление о том, что происходит с финансами компании
Давайте посмотрим, как Power BI Desktop позволит объединить данные и их визуализировать.
![Стартовое окно Power BI Desktop](https://habrastorage.org/files/74b/302/516/74b3025165dc49f9aa44a9f51b93c9ed.png)
Рисунок 1. Стартовое окно Power BI Desktop
При запуске Power BI Desktop просит указать источник данных или выбрать один из недавних источников. Кстати, по поводу источников – возможно подключение к большому их количеству, включая онлайн и локальные источники. Например, Google Analytics. В нашем случае все отчеты хранятся в одной папке, поэтому выберем в качестве источника «Папка». Такой выбор позволит в дальнейшем добавлять новые отчеты в эту папку и подгружать их данные для анализа нажатием кнопки «Обновить».
![Выбор источника данных](https://habrastorage.org/files/aa5/275/beb/aa5275beb79c46bc9cf8e7d5eb78d0af.png)
Рисунок 2. Выбор источника данных
После нажатия кнопки «Подключиться» отображается окно предварительного просмотра результатов запроса к источнику данных. Можно загрузить данные «как есть» или перейти в режим редактирования запроса, нажав на кнопку «Изменить». В большинстве случаев результаты запроса требуется обрабатывать.
![Предварительный просмотр результатов запроса](https://habrastorage.org/files/6a5/124/1d0/6a51241d069b4913a2adee45075663ba.png)
Рисунок 3. Предварительный просмотр результатов запроса
После нажатия кнопки «Изменить» автоматически открывается окно «Редактор запросов», в котором можно и нужно выполнить очистку и подготовку данных.
Рисунок 4. Окно редактора запросов
Как видно на снимке экрана, Power BI Desktop подгрузил файлы из папки и основную метаинформацию. Удалим все столбцы, кроме «Content», «Name» и «Extension». Поскольку имя файла показывает, к какому году относится соответствующий отчёт, мы его для этого и используем.
Если нажать на любую строку в столбце «Content», Power BI Desktop откроет содержимое соответствующей книги. Обратите внимание, что в разделе «Параметры запроса» все выполненные шаги записываются и их можно изменять или удалять. Если же открыть окно «Расширенный редактор», то откроется окно, в котором будет виден программный код для всех выполненных действий. Да в Power BI Desktop есть свой язык программирования “M” и это очень круто.
![Расширенный редактор запросов](https://habrastorage.org/files/44f/e8a/71f/44fe8a71ffc6479eab90a6d7291e0fd0.png)
Рисунок 5. Расширенный редактор запросов
Поскольку для формирование отчета требуется содержимое всех файлов Excel в папке, а не только одного, то я удалю два последних шага и использую некоторые функции языка “M” для парсинга содержимого книг Excel из папки.
Перед дальнейшей обработкой данных также нужно учесть то, что в папку могут быть подгружены не только файлы Excel. Поэтому нужно применить фильтр к столбцу «Extension», что позволит исключить ненужные типы файлов.
![Применение фильтра](https://habrastorage.org/files/f71/900/485/f7190048574d49ed82ba9d63c0c887d6.png)
Рисунок 6. Применение фильтра
Теперь нужно выполнить «извлечение» содержимого книг Excel. Для этого я добавлю новый столбец, используя функцию Excel.Workbook, которая позволяет «извлекать» содержимое книг Excel. Новый столбец содержит в себе значения типа «Таблица», что позволяет «развернуть» его содержимое на несколько других столбцов. При «развертывании» можно выбирать, какие столбцы будут отображены. В данном случае смысловую нагрузку несут столбцы «Data» и «Item»
![Добавление пользовательского столбца](https://habrastorage.org/files/b86/9f8/cf3/b869f8cf3c3a4b669f45856dbf6cb238.png)
Рисунок 7. Добавление пользовательского столбца
Столбец «Data» содержит в себе данные листов Excel, а «Name» и «Item» я в дальнейшем использую для временных отметок.
Поскольку столбец «Name» содержит данные вида yyyy.xlsx, где yyyy это год отчета, то выполним простую операцию разделения данных в столбце. Разделение можно выполнять как по количеству символов, так и по разделителю. В данному случае столбец нужно делить по разделителю.
![Разделение столбца](https://habrastorage.org/files/996/a61/66a/996a6166a42d45a394e5fa691e81e980.png)
Рисунок 8. Разделение столбца
![Окно настройки параметров разделения столбца](https://habrastorage.org/files/32f/ddf/b4e/32fddfb4e62a4925bfab786ae55bb79e.png)
Рисунок 9. Окно настройки параметров разделения столбца
После разделения столбца нужно будет его переименовать.
![Окно настройки параметров разделения столбца](https://habrastorage.org/files/32f/ddf/b4e/32fddfb4e62a4925bfab786ae55bb79e.png)
Рисунок 9. Окно настройки параметров разделения столбца
![Подготовленный к развертыванию запрос](https://habrastorage.org/files/d6e/5ce/680/d6e5ce680124424391df898627fc5b03.png)
Рисунок 10. Подготовленный к «развертыванию» запрос
Затем я «развертываю» столбец Data и вижу содержимое всех файлов и листов Excel, при этом в виде, который непригоден для построения итоговой отчетности. Но я могу использовать возможности Power BI Desktop для очистки данных.
![Запрос после развертывания содержимого файлов](https://habrastorage.org/files/151/bf9/281/151bf9281a804d3ab09e2d27c6ff5fd7.png)
Рисунок 11. Запрос после «развертывания» содержимого файлов
1. Использую верхние строки как заголовки и затем переименую столбцы. Удалю столбцы «Фактическое отклонение» и «Отклонение в %». В дальнейшем их пересчитаем.
2. Удалю строки, которые содержат пустые значения и значение «Показатель» в столбце «Показатель», применив фильтрацию. Таким же образом удалю строки, в которых содержатся суммарные значения, например «Итого ЧОД», «Итого» и т.д.
![Меню фильтрации данных](https://habrastorage.org/files/1fb/94a/278/1fb94a278c8f48f69bc5f27df3838cff.png)
Рисунок 12. Меню фильтрации данных
3. Используя функцию «Замена значений» выполню замену синонимов, например «Доход» и «Доходы».
![Замена значений](https://habrastorage.org/files/2bd/26b/9a7/2bd26b9a79d9486bbc6b3c81f10dbd98.png)
Рисунок 13. Замена значений
4. Еще немного изучив содержимое столбца «Показатель» обнаруживаю, что все доходы у меня относятся к показателю «Доход» или «Доходы». Всё остальное относится к расходам, что сильно облегчает задачу. Для удобства дальнейшей обработки и фильтрации создам столбец «Категория», который будет содержать значение «Доход», если в столбце «Показатель» присутствует слово «Доход», а во всех остальных случаях примет значение «Расход».
![Добавление столбца Категория](https://habrastorage.org/files/37b/5c7/86d/37b5c786d42340ada719109d2f446018.png)
Рисунок 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"
![Добавление пустого запроса](https://habrastorage.org/files/775/063/35e/77506335ed5f4f79889f59e35b3b34f7.png)
Рисунок 15. Добавление пустого запроса
9. Перехожу в запрос «План-Факт» и объединяю запросы, выбрав нужный тип объединения.
![Объединение запросов](https://habrastorage.org/files/4b9/222/ec2/4b9222ec284c4b719597824abd49cb6e.png)
Рисунок 16. Объединение запросов
10. Данные из запроса «Месяцы» добавились как новый столбец. Раскрываю его и теперь у нас есть все данные для формирования даты. Создаю новый столбец с названием «Дата», используя формулу: =Date.EndOfMonth(#date([Год],[Index],1)) Столбец добавлен и он содержит последнее число каждого месяца. Для того, чтобы Power BI Desktop мог группировать даты по месяцам и годам, нужно будет явно задать тип «Date»
![Добавление столбца Дата](https://habrastorage.org/files/5a3/0b3/839/5a30b3839ff247aca49f2a5b37f7e213.png)
Рисунок 17. Добавление столбца «Дата»
11. Удалим столбцы Index, Год и Месяц. Они нам больше не нужны. Затем нужно нажать на кнопку «Закрыть и загрузить», чтобы перейти к моделированию и визуализации данных.
На этом базовая часть обработки данных закончена и можно перейти к визуализации. Возможности визуализации данных в Power BI Desktop рассмотрим в следующей части.
Источник: https://habrahabr.ru/post/262655/
Комментариев нет:
Отправить комментарий