Шаблонизация (или параметризация) скриптов-источников для Power Query
Попалась мне тут интересная задача по доработке отчета: Каждая группа продуктов была представлена однотипным блоком из пяти скриптов в PQ. Запросы отличались друг от друга фильтрацией источника данных. Результаты этих запросов собирались в одну таблицу.
Мне требовалось добавить новую группу продукта и проделать много ctrl c и v, нигде не забыв подстроиться под новые данные.
Уверенная, что еще не раз попросят добавить данных, решила развлечь себя оптимизацией.
Шаг 1: Выделить общее из блоков запросов.
Создать шаблон к источнику данных, удалив из скриптов отличающиеся части для групп продуктов. Заменить эти части на "параметры". Фигурные скобки отлично подошли для их обозначения. В моем случае был запрос на DAX к табл. модели и запрос на MDX к OLAP-кубу.
Ниже пример как можно любую часть кода запроса заменить "параметром".
VAR
__fPrd = CALCULATETABLE (
DISTINCT ( 'Продукты'[ProductID] ),
{prodgroup}
{packing}
)Добавить данный шаблон скрипта в PQ как текст.
Шаг 2: Создать таблицу параметров. Для простоты на листе Excel, как на картинке.
- Название группы (вывод для пользователя)
- Параметр. Productgroup в нашем случае. Где прописать фильтр на DAX.
Шаг 3: В самом PQ в таблице параметров создать пользовательский столбец ссылающийся на наш текстовый шаблон. В результате на каждую строку таблицы параметров будет продублирован скрипт к источнику данных.
Заменяем {prodgroup} на значение колонки prodgroup. В итоге в колонке со скриптом рабочий запрос к модели. Создаем колонку с получением результата по этому скрипту.
AnalysisServices.Database( "servername" , "DBname" , [Query=[OurScriptColumn], Implementation="2.0"] )
Каждая строка таблицы параметров содержит таблицу результата скрипта. Разворачиваем ее. Доделываем нужные операции в PQ и выводим на лист пользователю.