Как сделать таблицу в power bi

Добавил пользователь Дмитрий К.
Обновлено: 29.08.2024

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

Кратко о возможностях Microsoft Power BI

Power BI — достаточно мощная и при этом бесплатная BI платформа. Microsoft вкладывает много средств в развитие этого продукта, в связи с чем ежемесячно выходят обновления, расширяющие его возможности. Вы можете использовать такие версии программы:

Power BI Desktop — предназначен для разработки модели данных и отчетов;
Power BI Service — онлайн-аналог, который специализируется на мониторинге и анализе готовых отчетов, поэтому возможности конструирования и работы с данными там очень ограничены.

Откуда можно загружать данные?

Power BI имеет множество встроенных коннекторов к различным сервисам и базам данных, с помощь которых вы в считанные минуты можете загрузить в программу нужный набор данных из различных источников, связать их между собой и построить консолидированные отчеты и диаграммы. На момент написания статьи все доступные коннекторы распределены на четыре группы:

SQL Server;
Access;
SQL Server Analysis Service;
Oracle;
IBM DB2;
MySQL;
PostgreSQL;
Sybase;
Teradata;
SAP HANA.

База данных Microsoft Azure SQL;
Microsoft Azure Marketplace;
Microsoft Azure HDInsight;
Хранилище BLOB-объектов;
Табличное хранилище Microsoft Azure;
Azure HDInsight Spark;
Microsoft Azure DocumentDB;
Хранилище озера данных Microsoft Azure.

Интернет;
Список SharePoint;
Канал OData;
Файл Hadoop;
Active Directory;
Microsoft ExChage;
Dynamics CRM online;
Facebook;
Google Analytics;
Объекты Salesfore;
Отчеты Salesforce;
ODBC;
R-скрипт;
appFigures;
GitHub;
MailChimp;
Marketo;
QuickBook Online;
Smartsheets;
SQL Sentry;
Stripe;
SweetIQ;
Twilio;
Zendesk;
Spark;
Пустой запрос.

Как видите, в Power BI можно импортировать данные из наиболее известных баз данных и сервисов, используя различные форматы файлов. После загрузки информации из доступных источников в Power BI, перед вами открываются большие возможности по очистке и преобразованию данных, в связи с тем, что в платформе присутствует достаточно мощный ETL функционал. Загружаемые таблицы можно редактировать, а на основе их столбцов создавать расчетные столбцы и меры — таким образом вы можете преобразовать массивы информации в наборы данных со структурой, необходимой для построения визуализации.

Какие визуализации можно построить в Power BI?

Сформировав набор данных, загруженных из различных источников, вам станут доступны множество встроенных элементов визуализации:

линейчатая диаграмма с накопление;
гистограмма с накоплением;
линейчатая диаграмма с группировкой;
гистограмма с группировкой;
нормированная линейчатая диаграмма;
нормированная гистограмма;
график;
диаграмма с областями;
диаграмма с областями с накоплением;
линейная гистограмма и гистограмма с накоплением;
линейная гистограмма и гистограмма с группировкой;
каскадная диаграмма;
точечная диаграмма;
круговая диаграмма;
диаграмма дерева;
карта;
таблица;
матрица;
заполненная карта;
воронка;
датчик;
многострочная карточка;
карточка;
ключевой показатель эффективности;
срез;
кольцевой график;
визуальный элемент r-script (на данный момент включается в параметрах программы).

Все элементы имеют достаточно широкий спектр настроек, направленных на изменение нужных параметров: цвет, фон, название, границы и так далее. Если вам будет недостаточно стандартного набора, можно загрузить пользовательские визуальные элементы.

Как построить отчёт в Power BI Desktop?

1. Установите связь Power BI с Google Analytics

1.2. Далее Power BI запросит вход в аккаунт Google.

1.3. Последний шаг — предоставить разрешение на просмотр данных в нужном представлении Google Analytics.

2. Как загрузить данные из MySQL в Power BI?

Чтобы показать функциональность Power BI, мы сгенерировали и загрузили в MySQL данные о продажах. Следуя описанным ниже инструкциям, можно подключать любые доступные базы данных и после соединять всю загруженную информацию в одну таблицу или график.

Теперь в модель данных Power BI загружены данные из двух источников: Google Analytics и MySQL.

3. Как установить связи между таблицами в Power BI

Для того, чтобы строить визуализацию на основе данных из двух различных источников необходимо настроить между ними связи.

Теперь наша модель данных состоит из пяти таблиц. Чтобы в этом убедиться, можно посмотреть в область полей либо перейти в режим визуального интерфейса просмотра модели данных (для этого кликните по иконке с изображением связей между таблицами на левой панели Power BI).

4. Как построить визуализацию в Microsoft Power BI?

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

4.3. Дополнительно можно воспользоваться опциями форматирования объектов визуализации, кликнув по иконке с кисточкой. В результате чего вы можете изменять цвета, размер, шрифт, фон и прочие параметры элемента визуализации.

5. Как настроить фильтры данных в Power BI?

В результате у нас получится лист с возможностью фильтрации данных по типу пользователя, источнику / каналу и месяцу года.

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

6. Как опубликовать информацию на Power BI Service?

Заключение

В статье было кратко рассказано:

Как загрузить в программу нужный набор данных (на примере интеграции с Google Analytics и загрузки данных из MySQL).
Как упорядочить данные, чтобы привести их к нужному для визуализации виду.
Как построить отчет в Power BI, где будут отображаться данные из разных источников.
Как настроить фильтры данных, выбрать подходящие элементы визуализации и опубликовать результат для просмотра в онлайн-версии Power BI.

Microsoft Power BI — мощный и многофункциональный инструмент, поэтому достаточно тяжело описать весь его функционал в рамках одной статьи. Ответы на базовые вопросы по работе с Power BI можно узнать из официального мануала, который вы можете найти по этой ссылке.

О НАС

Bintels — консалтинговая компания, специализирующаяся на разработке и внедрении бизнес-аналитики на базе Microsoft Power BI. Проводим корпоративные тренинги по Power BI

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

Разбираться с Power BI владельцам и сотрудникам микропредприятий не рекомендую. Продукт полезен в работе с большими объемами данных, а для меньших форматов есть инструменты попроще – например, тот же Google Data Studio.

Если информации мало или она однотипная, использование системы не окупится – больше времени и сил уйдет на настройку. Зато для предприятий с несколькими каналами продаж и рекламы, сложной CRM и распределенными базами финансовых данных Power BI станет настоящей находкой.

Продукт оптимально подходит для трех основных сценариев использования:

Руководителям: для динамического контроля показателей и тенденций. Достаточно настроить панель мониторинга, а дальше все будет работать само по себе.
Менеджерам: для детального обзора и ускоренного анализа бизнеса. Им пригодятся и сами отчеты, и автоанализ данных.
Аналитикам: для поиска новых возможностей и обнаружения узких мест. Они смогут выстраивать настолько изощренные модели данных, насколько позволяет мышление.

Для начала нужно создать аккаунт Microsoft на странице Power BI. Личная учетная запись MS Live, которая есть у многих, здесь не годится, нужна именно рабочая или учебная. При регистрации обязательно вводите корпоративный email – почтовые адреса от бесплатных сервисов не подойдут.

Имейте ввиду, что Microsoft может отказать в регистрации российского аккаунта по неизвестной причине. Проблема решается просто: используйте те же данные, но смените страну на Великобританию или США.

После регистрации вы окажетесь на главной странице. На нее выводятся последние открытые отчеты и панели. Здесь же отображается избранное.

Лучше сразу скачайте и установите Power BI Desktop – он пригодится позже для подключения источников данных.

Структура сервиса

В экосистему продукта входит три элемента: веб-сервис, приложение Power BI Desktop для Windows и мобильные приложения для iOS, Android и Windows Mobile. Каждый из них отличается по функциональным возможностям.

В своей прошлой статье я рассказывал про программные возможности языка SQL и обещал поделиться кейсом по созданию автоматизированного отчета на основе стека технологий MS SQL Server и Power BI.

Почему именно эти технологии?

За время работы аналитиком, я перепробовал различные варианты сбора отчетности. Начиная с ручной выгрузки данных из кабинетов рекламных систем, с последующим сведением в Excel, и заканчивая созданием специальных отчетов в Google Analytics или дашбордов в Data Studio.

Но ни один из вариантов не был идеальным и каждый имел свои недостатки. Все изменилось, когда я открыл для себя Power BI.

Microsoft Power BI — это один из самых технологичных на данный момент инструментов по визуализации данных, обладающий большим набором коннекторов к различным системам.

Но и Power BI сам по себе не идеален и без грамотного использования будет работать медленно и неэффективно. Приведу два примера:

Если вы попытаетесь собрать модель данных из различных источников, с большим количеством связей и рассчитываемых показателей на стороне Power BI, то отчет будет жутко тормозить, а ведь именно таким принципам работы учит большое количество курсов по данному инструменту.
Еще пример, если вы пытаетесь загрузить в модель данные из Google Analytics при помощи встроенного коннектора, то столкнетесь как минимум с двумя проблемами — ограничениями API GA и долгой выгрузкой данных.

Вышеописанные проблемы привели меня к мысли о загрузке всех данных сначала в базу, моделировании отчета при помощи SQL и только потом их визуализации в Power BI.

Переходим к делу

Для примера возьмем задачу по автоматизации отчета по эффективности контекстной рекламы.

К данному отчету заказчиком предъявляются следующие требования:

Отчет должен содержать исторические данные по вчерашний день;
Отчет должен обновляться ежедневно в автоматизированном режиме;
Помимо Power BI, должна быть возможность подключения к отчету через Excel.

Также отчет должен содержать следующие параметры и показатели:

Естественно, все данные должны быть предварительно загружены в хранилище, но это тема отдельного поста и обычно этим занимаются data-инженеры. Мы же с вами аналитики и используем те данные, которые для нас любезно сложили в DWH (хранилище данных).

В моем случае DWH работает на базе MS SQL Server и содержит следующие таблицы:

sessions — данные из Google Analytics загруженные посредством коннектора к Reporting API v4;
costs — данные по расходам, предварительно загруженные в Google Analytics;
orders — данные по заказам и доходу из внутренней CRM-системы.

Для работы нам потребуется установить:

Опущу совсем уж базовые вещи, такие как регистрация аккаунтов и установка программ, с этим вы без проблем справитесь и сами.

Готовим данные

Итак, задача понятна, инструменты готовы — за дело!

Создаем таблицу

Для того чтобы создать отчет, нам необходимо свести данные по расходам, сеансам и заказам в одной таблице. Для этого напишем SQL-запрос, в котором объединим таблицы по следующим ключам:

date ;
sourceMedium ;
campaign .

Но вернемся к задаче и после некоторых манипуляций с SQL получим вот такой скрипт:

Запустим его и порадуемся получившемуся результату:

Создаем таблицу

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

Гораздо более правильным решением будет создать промежуточную таблицу в базе данных и докладывать туда ежедневно данные за прошедшие сутки. Что мы и сделаем:

Таблица будет иметь следующую структуру (подробнее о типах данных):

При сохранении таблицы укажем название:

И теперь, чтобы получить все данные из нее, достаточно выполнить простой SELECT :

Создаем хранимую процедуру

Отлично! Настало время автоматизации ?

А поможет нам в этом функционал хранимых процедур (подробнее рассказывал о них тут).

Засучим рукава и обернем наш скрипт в код процедуры:

Теперь протестируем и вручную вызовем процедуру:

Скорость отработки процедуры 3 секунды на одном дне — вполне приемлемо. Проверим появились ли данные в ранее созданной таблице:

Осталось настроить ежедневное обновление.

Настраиваем расписание

Зайдем в агент и добавим новое задание:

Укажем название и придумаем описание:

Далее создадим новый шаг, в котором будем вызывать процедуру с данными за прошедшие сутки (обратите внимание, объявление переменных с датами из нашего скрипта мы перенесли в расписание и немного изменили):

Настраиваем время запуска, периодичность и сохраняем:

Теперь данные автоматически будут поступать в отчет ежедневно в 9 утра.

Визуализируем данные

Данные готовы, обновление настроено, самое время приступить к визуализации.

Останавливаться на том как установить Power BI и как им пользоваться не буду, так как этой теме посвящен целый урок нашего курса.

Создаем отчет

Заходим в desktop-версию Power BI и открываем коннектор к SQL Server:

Вводим данные для подключения к серверу, название базы данных и наш короткий SQL-запрос к ранее созданной табличке:

И это все! Никаких сложных моделей в Power BI строить не нужно, так как мы уже это сделали на стороне SQL-запроса.

Наиболее правильным считаю подход, когда инструмент визуализации используется именно для этой самой визуализации и еще для создания рассчитываемых показателей (например, CPC, CPO, ROMI). Используйте эти рекомендации и ваши отчеты будут летать.

После того как будет готов дизайн отчета, его нужно загрузить в облако Microsoft:

Настраиваем расписание

Отчет опубликован! Остался финальный шаг, для этого переходим в веб-версию Power BI и настраиваем расписание обновления.

Но перед этим не забываем поставить на компьютер, с которого будет происходить обновление, локальный шлюз Power BI (а лучше всего завести под это дело отдельную виртуальную машину):

Важно так подгадать расписание, чтобы оно запускалось в тот момент, когда на стороне SQL Server уже отработает наша процедура и положит в табличку свежие данные. Плюс нужно заложить небольшой запас времени, на возможные проблемы с сервером при его перегрузке:

А как же Excel?

Иногда заказчики могут попросить загрузить данные в Excel для более детального анализа.

После чего останется только указать SQL-запрос и сохранить:

С этого момента данные из нашей таблицы на сервере станут доступны в Excel.

В итоге мы получили автообновляемую отчетность, без привлечения каких-то гигантских ресурсов разработки и без особых денежных затрат.

Научитесь использовать функционал программных продуктов Power BI и Power Query для ускорения обработки данных и их визуализации.

С появлением мощных компьютеров, технологий программирования и, что не менее важно, с увеличением потребностей специалистов в оптимизации труда, компьютерная сфера беспрестанно развивается, создавая самые актуальные программы и приложения. Есть программы, которыми пользуются профессионалы, работающие в любой сфере во всех странах мира. Если вы еще не догадались, то речь сегодня пойдет об универсальном средстве обработки данных – Microsoft Excel.

Визуализация – это одна из возможностей, которая делает Excel одним из самых популярных приложений в мире. Качество графиков и диаграмм очень велико. В графическом функционале Excel можно найти все, что необходимо для красочной и яркой визуализации. Но создать в программе отчет или дэшборд не очень удобно, а порой и нереально!

Корпорация Microsoft позаботилась об этом и разработала приложение Power BI, полностью интегрирующееся с Excel, в котором можно было бы создавать продвинутую графику, делать объёмный анализ и обмениваться данными.

Многие пользуются Excel, но не все знают, как удобно работать с данными из Excel в Power BI. Об этом мы сегодня и расскажем.

Предположим, что вас попросили за короткий промежуток времени предоставить хороший отчет о продажах за последний период и высказать свое мнение о проведенных оптимизационных действиях компании. Это несложное задание, да. Но на него нужно время, особенно, если учесть, что информации проанализировать надо много, а находится она, скорее всего, в разных источниках. В таких ситуациях навык работы с Power BI окажется как нельзя кстати. Итак, начнем с небольшого вступления.

Power BI – это бесплатное (условно) приложение для обработки, анализа и визуализации данных. Главными преимуществами программы является возможность получать данные из любых источников и делиться ими с вашими коллегами.

Даже бегло пробежав по списку источников, доступных Power BI, понятно, что в нем есть все самое необходимое. Но нас интересует соединение Power BI именно с Excel.

Excel и Power BI хорошо работают вместе. Это позволяет нам совместно использовать два инструмента для эффективного анализа бизнес-процессов и экономить время на достаточно простой, но объемной работе. Power BI поддерживает любые книги Excel: книги с диапазонами и таблицами, с моделями данных, книги со сводными таблицами и диаграммами, а также книги, содержащие подключение к внешним источникам. В приложении можно работать с различными типами данных: числовыми (целые и десятичные числа), текстовыми, логическим (TRUE/FALSE), с датой и валютой. Если в Excel указать конкретные типы данных, то это увеличит эффективность Power BI.

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

Подготовка листа Excel

Предположим, что у нас есть книга Excel, содержащая информацию о продажах за какой-то период:

Лист с информацией по продажам

Нужно проверить, чтобы все столбцы содержали однородную информацию (одного типа) и чтобы в таблице была строка с заголовками столбцов.

С заголовками все в порядке, и данные в столбцах одного типа!

Теперь отформатируем данные в виде таблицы.

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

Мы выполнили всю необходимую подготовку. Всего минута, и лист Excel готов к отправке в Power BI. Переходим к следующему шагу.

Подключение к книге Excel из Power BI

Нас интересуют данные из Excel:

После того, как мы выбрали необходимый файл, данные загрузились в программу, и теперь мы можем с ними работать и создавать отчеты.

В Power BI мы можем изменять наши данные: удалять или добавлять столбцы/строки; указывать типы данных для столбцов, если они не были указаны в Excel.

В появившемся окне можно изменять загруженную таблицу:

Но мы пока оставим этот момент и перейдем к самому интересному – к созданию отчета.

Создание визуализации

Перед нами появился пустой белый лист, на котором мы будем располагать графики.

Справа находится панель для управления визуализацией и поля с данными:

Огромное преимущество визуализации в Power BI – возможность следить за разными показателями на одной странице. Составим небольшой дэшборд на основе данных, которые мы загрузили из Excel! Наши данные содержат информацию о покупателях, покупках, ценах, прибыли, скидках, дате покупок и другие показатели. Построим график, который отображал бы информацию о продажах по странам.

Сначала определимся с типом диаграммы.

Можно обойтись обыкновенной диаграммой, а можно разнообразить отчет пузырьковой диаграммой, где вместо координатной сетки будет карта мира.

Посмотрим, как это сделать:

Получаем следующую диаграмму:

Теперь построим диаграмму продажи и прибыли по сегментам:

Для красоты мы изменили название графика, добавили заливку фона названия в цвет столбцов, выровняли по правому краю легенды и добавили подпись данных на диаграмме:

Теперь построим графики продаж и прибыли по месяцам с помощью диаграммы с областями:

Наш отчет просто не может обойтись без графика продаж, сгруппированных по продуктам.

Воспользуемся круговой диаграммой:

Отформатируем и приведем к понятному и читабельному виду:

Давайте посмотрим, что у нас получилось:

Мы смогли за довольно короткое время создать очень яркий, информативный, а главное – легкий для восприятия отчет.

А еще наш дашборд интерактивный. Это значит, что любые изменения в одной диаграмме влияют на другие. Мы можем смотреть одновременно на все показатели, а можем выделять отдельные интересующие нас данные.

Например, если мы хотим посмотреть данные только по США, то выделив на карте кружок, относящийся к Америке, все остальные диаграммы автоматически изменятся:

Теперь мы можем видеть, как менялась прибыль по месяцам, чему равна прибыль по продуктам, как распределены продажи и прибыль по сегментам и другое только для США.

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

Сохранение дашборда

Если вы авторизованный пользователь, а компьютеры в организации связаны общим BI аккаунтом, то после публикации данные будут доступны всем пользователям:

Эпилог

Мы продемонстрировали всего лишь часть возможностей Power BI. Это очень удобное приложение особенно полюбится тем, кто только прошел наши курсы по изучению MS Excel.

Power BI расширяет функционал Экселя и дает возможность анализировать данные в режиме онлайн, а также встроенные методы анализа чрезвычайно выручают, когда поджимают сроки.

Безусловно, у Power BI есть конкуренты, но он уверенно держится на рынке, постоянно обновляя и пополняя свой функционал и расширяя предоставляемые возможности. А интуитивно понятный интерфейс и сходство с Excel только добавляют ему популярность.

Поэтому, Power BI – прекрасное программное средство в арсенале любого профессионала: финансиста, аналитика, менеджера или статиста.

POWER BI И POWER QUERY

Автор: Андрон Алексанян, практикующий Data Scientist и COO сети медицинских офисов.

Читайте также: