Передача Параметров из Power BI в скрипт на Python

После установки августовского релиза Power BI я очень обрадовался, увидев поддержку Python. Как раз в работе оказался проект, в котором возможность поддержки Python помогла реализовать требования заказчика. Нужно было подключаться к API, предварительно зашифровывая тело запроса открытым и закрытым ключём, перед отправлением запроса к API. К сожалению, как оказалось, поддержка шифрования в Power BI есть только в кастомных дата коннекторах, и отсутствует в Power Query для Power BI Desktop. А при всем моем теплом отношении к языку R, связываться с ним лишний раз, мне бы не хотелось. Поэтому поддержке Python в Power BI я был рад очень.

 

Задача

Настроить управление поведением скрипта на Python через параметры отчета Power BI Desktop.

Описание решения

Предварительные требования

  1. У вас установлена Anaconda
  2. В Power BI включена поддержка Python
  3. Запуск в терминале jupyter notebook
  4. Откройте памятку по Python

 

Подготовка в Power BI

  • В файле Power BI создаем параметры отчета.
  • Оборачиваем параметры отчета в таблицу с необходимой структурой. Если используются параметры типа date/datetime, то предварительно их приводим к строке:

 

Разработка скрипта Python

В моем примере скрипт на Python будем разрабатывать в инструменте, популярном у аналитиков данных Jupyter Notebook.

  • Создаем файл блокнота.
  • Импортируем библиотеку работы с таблицами Pandas (структура аналогичная объекту Table в Power BI)

import pandas as pd

  • Объявляем переменную Parameters повторяющую структуру, таблицы с параметрами на Power Query (см. выше), это необходимо для отладки и проверки работоспособности скрипта.

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

  • Далее реализовываем логику обработки скрипта.
  • Для удобства переноса разработанного скрипта, обработки снова объявляем импорт библиотеки Pandas.

import pandas as pd

  • Помним о том, что мы передали параметры в формате даты времени в текстовом формате ( объяснение подробнее см ниже )

Parameters[‘FromDate’] = pd.to_datetime( Parameters[‘FromDate’], format=“%d.%m.%Y” )

Parameters[‘ToDate’] = pd.to_datetime( Parameters[‘ToDate’], format=“%d.%m.%Y” )

  • Получаем данные из внешней системы. Для демо примера создаем вручную таблицу в Pandas

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

Sales[‘Date’] = pd.to_datetime(Sales[‘Date’],format=“%d.%m.%Y”)

  • Применяем бизнес правила полученные от клиента и параметры, полученные из Power BI, к полученной в скрипте таблице

Result = Sales[ Sales[‘Date’] > Parameters[‘FromDate’][0] ]

  • Средствами Jupyter Notebook мы можем отладить и проверить полученные данные перед копированием полученного сприпта в Power BI.
  • В результате получаем скрипт, подготовленный для передачи в Power BI

 

Вызов скрипта на Python из Power BI

  • Так как, на данный момент, в мастере создания скрипта на Python в Power BI нет возможности передать не обязательные параметры, для простоты создаем минимально рабочий пример для создания корректного запроса Power Query, который затем обновим разработанным выше скриптом.

 

import pandas as pd

Dummy = pd.DataFrame( [ [ “” ] ], columns = [‘Dummy’] )

 

  • В мастере создания запросов Power BI Desktop, выбираем из раскрывающегося списка скрипт на Python. Передам минимальный скрипт на Python написанный выше.
  • В мастере выбираем флажком таблицу Dummy и нажимаем OK.
  • Скрипт будет успешно создан.
  • Для передачи параметров нам придется перейти в расширенный редактор Power Query, где мы увидим следующий текст:

  • Последний шаг навигации нам пока что не нужен, уберем его.

 

  • Передадим параметры из Power Query в пустой скрипт. С помощью ручного редактирования в расширенном редакторе Power Query добавим второй параметр вида [ Parameters = Parameters ].
  • В результате получится следующий запрос на Power Query:

  • Power Query предупредит что мы изменили определение скрипта. Мы должны согласиться с изменениями. В результате нам будет доступно для навигации две записи каждая из которых содержит таблицу.
  • Отредактируем с помощью мастера запрос и вставим разработанный на предыдущем шаге скрипт, нажав на шестеренку напросив пункта Source.
  • В результате получится следующий запроса на Power Query:

 

Обратите внимание на то, что все наше красивое форматирование в python Power BI Desktop собрал в одну строку. Так что если далее потребуется дорабатывать наш скрипт мы вернемся в Jupyter и последовательно проделаем шаги: исправим, отладим и так же передадим его через буфер обмена в Power BI Desktop.

  • В результате у нас будет доступны все источники данных в формате DataFrame которые мы объявили в скрипте и далее средствами Power Query как мы привыкли будем обрабатывать таблицы и объединять их в модель.

 

Материалы

Для удобства исследования статьи привожу готовый файл срипта и пример на Power BI:

 

Обсуждение решения

  • Из описания помощи к функции power query Python.Executeвидно, что функция получает два параметра function (script as text, optional arguments as nullable record) as table. Обязательный script собственно текст скрипта на Python и не обязательный arguments. Опытным путем понял, что корректным форматом передачи аргументов внутрь скрипта является таблица. Таким образом для передачи аргументов нужно создать запись у которой есть хотя-бы одно значение содержащее таблицу. В нашем случае мы создали запись вида [ Parameters = Parameters ]. Где справа ключ записи, а справа ссылка на таблицу содержащую параметры.
  • При подготовке примера оказалось что у Power BI при передаче параметров есть особенность работы с типом date/datetime.
  • Дату и время в power query нужно приводить к строке.
  • А внутри скрипта на python приводить обратно к типу date/datetime.