Python в Power BI 101

Опубликовано: 18 июл 2021

Эта статья о том, как подтянуть с помощью Python данные практически из любого источника внутрь Power BI и преобразовать их для дальнейшего анализа. Статья не про то, что это нужно делать, а скорее про то, что это можно сделать. Она возможно покажется интересной тем, кто хорошо знает Python и SQL и только начинает знакомиться с Power BI. На секунду мы представим "идеальный мир", в котором знание Python и SQL достаточно, чтобы построить любые отчеты для стейкхолдеров в данном инструменте. В реале, это конечно же не совсем так — Power Bi, как сложный агрегат, требует знания или хотя бы понимания функционала его различных модулей, в этом плюс и где-то минус данного инструмента и одна из причин, почему эта статья была написана.

1. Подготовка

Мы будем запускать Python внутри Power BI, но сам Python и дополнительные модули должны находится на нашем компьютере. Мы можем создать виртуальное окружение внутри какой-нибудь выбранной директории и установить в окружении все нужные нам модули. В этой статье для примера мы будем подтягивать данные из PostgreSQL, поэтому я дополнительно устанавливаю модуль psycopg2:

virtualenv env
source env/Scripts/activate

pip install pandas
pip install matplotlib
pip install psycopg2

В настройках Power BI нам нужно указать директорию, где находятся исполняемые модули Python:

Меню File >> Options and settings >> Options >> Python scripting.

Здесь указываем директорию нашего виртуального окружения в пункте "Set a Python home directory".

2. Выбор источника данных в Power Bi и создание Python скрипта

После старта Power BI мы выбираем источник данных, кликнув Get Data. В поиске набираем "python" и кликаем Python script и затем Connect. В открывшееся окно мы поместим наш скрипт.

Для примера мы будем подтягивать данные из таблицы Orders в PostgreSQL. Забегая вперед, скажу, что нам понадобятся список названий колонок нашей таблицы. Получить мы их можем с помощью простого SQL запроса. Я обычно использую Jupyter notebook, там получить список можно двумя действиями:

--подтягиваем данные
%%sql columns <<
SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name   = 'orderes'

#выводим список, чтобы скопировать его
print(list(columns.column_name))

Наш Pyhton скрипт будет состоять из нескольких простых частей:

импортируем нужные модули
указываем параметры соединения с базой данных в PostgreSQL
создаем соединение с БД
запускаем SQL зарос, который должен вернуть нам данные о сделанных заказах на выбранном временном интервале
помещаем результат в pandas DataFrame

import pandas as pd
import psycopg2
con = psycopg2.connect(
    dbname='orders', 
    user="postgres", 
    password="password", 
    host="host",
    keepalives=1,
    keepalives_idle=30,
    keepalives_interval=10,
    keepalives_count=5)
cur = con.cursor() 
con.autocommit = True
cur.execute("""
        SELECT * FROM orders WHERE datetime > current_date-7;
    """)
rows = cur.fetchall()
col = ['order_id', 'service_id', 'action', 'amount', 
 'title', 'product', 'rateperamount', 'promoprice', 'analytics', 
'datetime',  'user_id', 'user_currency',  'ordertotalprice', 'promo']
df = pd.DataFrame(rows, columns=col)

Вставляем код скрипта и нажимаем OK, если соединение с источником прошло успешно откроется окно Navigator. Здесь выбираем нашу таблицу df и кликаем Transform Data. Если вы уверены, что тип ваших данных будет определен 100% точно, вы можете на этом этапе выбрать не Transform Data, а Load.

Чаще всего нам приходится менять типы данных. В открывшемся экране Power Query Editor мы можем выбрать нужную нам колонку и поменять тип данных с помощью пункта меню Data Type. В данном случае я хочу поменять тип Text на Fixed decimal number для колонки, где указана стоимость:

И получаю ошибку:

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

File >> Options and settings >> Options >> Regional Settings

Поменяв здесь настройки убираем последний шаг в блоке Applied Steps на экране Power Query Editor и задаем новый тип данных для выбранного столбца заново. Теперь ошибки быть не должно. В большинстве случаев нужно отслеживать, что цифровые данные имеют правильный формат чисел, и данные типа datetime имеют тип Date/Time или Date/Time/Timezone, если ваши данные уже учитывают часовой пояс.

Нажимаем Close & Apply чтобы вернуться на основной экран редактировани отчета, где нас может ждать сообщение "Build visual with your data". Let's do it!

3. Построим график количества заказов в разбивке по дням

Для создания простейшего отчета нам потребуется:

новая вычисляемая метрика количества заказов в нашей модели данных df
существующая переменная datetime для группировки по дням
график типа Line Chart
Slicer для выбора дат

Cоздаем новую метрику в нашей моделе данных — количество заказов. Кликаем правой кнопкой на поле, например, order_id и затем кликаем New Measue. В строчке формулы водим значение:

Orders = DISTINCTCOUNTNOBLANK(df[order_id])

Новая метрика Orders создана.

Перейдем к построению графика. Кликаем на иконку нужного графика Line Chart, он появится на экране отчета, отрегулируем его размеры. Пока он по-прежнему выделен, выберем значения по которым его будем строить - datetime и Orders. В результате получаем такую картину:

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

В блоке Axis кликаем правой кнопкой мышки на datetime и вместо Date Hierarchy выбираем datetime.

Получаем такую картину:

Тоже не то, что нам нужно, поскольку Power BI пока не понимает, что нам нужно сгруппировать данные по дням. Чтобы сгруппировать данные на временном интервале правильным образом еще раз кликаем на datetime и затем на New Group. В открывшемся окне задаем группировку 24 часа:

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

Отменяем выделение нашего графика, кликнув мышкой на свободном пространстве и добавляем в отчет Slicer, кликнув на его иконке и отредактировав его размер. Перетаскиваем в него поле Date (группированное поле datetime) и убираем крайнюю дату, за которую данные собраны еще не полностью. Для удобства отображения увеличиваем размер шрифта для поля Date Inputs.

Не забываем сохранить файл.

Ради интереса мы можем сделать быструю проверку данных с помощью аналогичного SQL запроса к нашей БД. Чтобы видеть цифры по дням в отчете Power BI переключим тип визуализации нашего графика на Table. Теперь можно запустить соответствующий запрос к БД в Jupyter notebook и сравнить результат.

%%sql df <<
select count(distinct order_id), date(datetime) date from orders
where datetime >= current_date-7
group by date
order by date

	count	date
0	477	2021-07-10
1	419	2021-07-11
2	1291	2021-07-12
3	1231	2021-07-13
4	1304	2021-07-14
5	1213	2021-07-15
6	1155	2021-07-16
7	108	2021-07-17

Все совпадает.

Как мы видим в таком подходе мы можем трансформировать данные, как на этапе python скрипта c использованием функций Pandas DataFrame, так и потом, используя нативные методы трансформации данных Power BI.

← Продуктовая аналитика главного меню сайта

Потайная дверь в A/B тестирование →