Создание решения дата мэппинга для Power BI на базе Power Apps и Power Automate

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

Обычно проблема решается при помощи создания справочников на базе MS Excel в OneDrive и Google Spreadsheets. Если не брать в расчет проблемы с возможными ошибками в этих файлах в результате, которых возникают ошибки при обновлении отчета, то их ключевая проблема в поддержании их актуальности. Новые значения требуется добавлять в таблицу, а выявлять и добавлять их вручную трудозатратно.

Рассмотрим создание решения для data mapping на базе Microsoft Power Apps для автоматизации этого процесса. Данное решение позволит нам обеспечить следующий функционал:

• автоматическое появление в интерфейсе новых значений, которые появились в источнике данных

• переключатель отображения только нераспределенных значений

• сохранение справочника в базу данных или в файл

• минимизация ошибок ручного ввода, которые будут ломать обновление отчета

• создание понятной бизнес-логики с учетом юзабилити для бизнес-пользователя

• возможность создания и редактирования правил для автоматического маппинга через графический интерфейс

Примеры решаемых задач:

• выгружаем данные по оказанным услугам из информационной системы и добавляем к каждой услуге категорию и направление

• подключаемся к google analytics и извлекаем данные utm_medium, utm_source, utm_campaign. Далее мы хотим разметить разные рекламные кампании по разным направлениям услуг, чтобы отслеживать эффективность рекламы в разрезе направлений и отделов компании, а также разметим типы трафика (SEO, e-mail, контекстная реклама и т. д.)

• распределяем специалистов по различным направлениям и задаем определенный процент с продаж для расчета заработной платы

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

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

• Power BI для соединения и обработки данных

• Power Automate для автоматизации и добавления данных из Power BI в источник данных

• Power Apps для создания формы просматривания и редактирования нужных данных

Этап 1 — Обработка данных, работа в Power BI

Первым делом необходимо решить какую таблицу данных мы будем использовать для редактирования данных. В качестве исходных условий мы рассмотрим ситуацию, когда у нас уже создан поток данных в Power BI, в который загружена нужная нам таблица, из какого-либо источника данных. Далее мы обрабатываем нужную нам таблицу в Power BI, если это необходимо. Например, мы можем удалить ненужные строчки или столбцы, или отфильтровать таблицу от ненужных нам значений. Всё это можно проделать как настольной версии Power BI Desktop, так и в облачной версии Power BI Service, в редактировании потоков данных.

Далее мы переходим непосредственно к отчёту в Power BI, и добавляем туда кнопку Power Automate

На листе дашборда Power BI получается следующий внешний вид

Далее мы кликаем правой кнопкой мыши на саму кнопку, и выпадающем списке выбираем поле «Изменить»

Этап 2 — Настройка Power Automate

Далее в появившемся меню пролистываем вниз и выбираем, «Обновление таблицы в Excel из Power BI» из рекомендуемых шаблонов Power BI

Далее откроется экран проверки подключений, где мы нажимаем «Продолжить»

Теперь перед нами открылось непосредственно само меню настройки Power Automate, где мы удаляем ненужный шаг «Update a row», в нашем случае он нам не потребуется

После чего добавляем новый шаг «обновить поток данных». Этот шаг нужен чтобы данные были всегда актуальны. В этом шаге мы заполняем соответствующие поля

Далее добавляем шаг «Задержка», чтобы дать время обновиться потоку данных, в нашем случае это будет 15 минут

После чего добавляем еще один шаг «обновить набор данных». Этот шаг также нужен чтобы данные были актуальны уже после обновления потока данных. В этом шаге мы также заполняем соответствующие поля

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

Последним шагом будет «Добавление строки в таблицу» в Excel Online, где мы заполняем все поля. Именно этот шаг отвечает за заполнение источника данных, изменёнными нами данными в Power BI

Таким образом у нас получаются 6 шагов:

Далее мы нажимаем «Сохранить и применить»

После чего при нажатии кнопки будут выполнятся добавленные нами шаги

Чтобы убедится, что все шаги были выполнены успешно мы можем снова зайти в меню изменения настройки «Power Automate», как было показано на рисунке выше. После чего выбрать созданный нам поток в нашем случае «Последняя версия»

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

Этап 3 – Создание приложения в Power Apps

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

Открываем Power Apps

Далее нажимаем «создать приложение», после чего перед нами где мы должно выбрать формат – Планшет

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

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

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

Чтобы работать с данной таблице в Power Apps, нам нужно создать таблицу в Excel, для этого мы просто выделяем нужные нам ячейки и нажимаем «таблица» во вкладке «Вставка», как показано на рисунке

В нашем случае получилось следующая таблица, далее мы с ней будем работать:

Теперь, когда Excel файл готов, можем приступить к редактированию непосредственно формы в Power Apps.

Чтобы подключить нашу таблицу к Power Apps, выберем вкладку «Данные»

Далее в нашем случае мы выбираем вкладку «Соединители»

И выбираем OneDrive на котором и хранится наш Excel файл, там же выбираем нужный на файл

Теперь у нас есть подключение к нашей таблице:

Можем приступать к редактированию формы. В нашем примере у нас получилась следующая форма:

В которой конечный пользователь сможет заполнять недостающие данные

Пройдёмся по основным моментам. Различные графические элементы мы можем найти на вкладке «Вставка»

Для изменения свойств элементов существует панель справа

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

Используя все вышеперечисленные инструменты, мы можем создать свое собственное приложение.

Для начала создадим пустую вертикальную коллекцию, из вкладки «Вставка» которая располагается в левой части экрана.

Внутри коллекции мы добавим поля из вкладки «Ввод»

В нашем случае были добавлены два текстовых поля и три раскрывающихся списка

Названия столбцов мы добавляем над нашей коллекцией в виде отдельного компонента «Метка»

Теперь чтобы раскрывающиеся списки показывали нам только уникальные значения, добавим функцию Distinct в свойствах Items, как показано на рисунке ниже, где data555 это наш источник данных, а «Направление» — это столбец значений для вывода.

Далее добавим в нашу форму кнопку удаления строки, для этого выберем подходящий значок. Теперь чтобы кнопка работала, для события OnSelect, используем функцию Remove, где data555 наш набор данных и ThisItem – эта строка.

Теперь перейдём к передаче наших изменений из Power Apps в наш источник данных. Для этого мы создадим Коллекцию в Power Apps в которую будут записываться все наши изменения и из которой при нажатии кнопки «Сохранить» будут отправляться наши изменения в источник данных.

Для чего нам нужно нажать на само приложение, которое называется «App» и в событии «OnStart» прописать похожий код для создания коллекции, где data555 это наш источник данных:

ClearCollect(colTaskUpdates,Defaults(‘data555’)

Таким образом мы создали коллекцию «colTaskUpdates», её можно посмотреть во вкладке «Просмотр»

Теперь мы можем посмотреть в нашей коллекции названия наших столбцов, и чтобы оставить в нашей коллекции только нужные столбцы мы изменяем код, который теперь выглядит следующим образом:

ClearCollect(     colTaskUpdates;      ShowColumns(         Table(Defaults(data555));         "***";         "***";          "***";          "***";         "***"     ) );;  Clear(colTaskUpdates)

где вместо:»***», могут быть названия ваших столбцов

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

Чтобы мы могли передавать наши изменения в коллекцию, добавим в событие «OnChange» код выбранных нами элементов

В нашем случае мы выбрали три раскрывающихся списка изменения в которых будут передаваться в коллекцию «colTaskUpdates», а из нее в наш источник данных.

Полный код выглядит так:

If(     ThisItem.'***' in colTaskUpdates.***;     Update(         colTaskUpdates;         LookUp(             colTaskUpdates;             ***=ThisItem.'***'         );         {             ***:ThisItem.'***';             ***: Dropdownnapravl.SelectedText.Value;              ***: Dropdowngrup.SelectedText.Value;             ***: Dropdownrazdel.SelectedText.Value;             ***: ThisItem.'***'         }     );     Collect(         colTaskUpdates;          {             ***:ThisItem.'***';             ***: Dropdownnapravl.SelectedText.Value;              ***: Dropdowngrup.SelectedText.Value;             ***: Dropdownrazdel.SelectedText.Value;             ***: ThisItem.'***'          }     ) )

где ThisItem.’***’ in colTaskUpdates.***; — это столбец с уникальными значениями,

*** — название столбца из коллекции (они отличаются от названий из источника данных),

Dropdownnapravl — это название элемента управления

Теперь все изменения из наших элементов, показанных на рисунке, будут записываться в коллекцию

Теперь чтобы передать изменения из коллекции в источник данных, добавим кнопку «Сохранение». Для этого добавим кнопку из вкладки «Вставка»

В событие «OnSelect» для этой кнопки добавим код

If(     CountRows(colTaskUpdates) > 0;     Patch(         data555;         colTaskUpdates     );;     Notify(         "Успешно";         NotificationType.Success     ) );; Clear(colTaskUpdates);;

где data555 — это наш источник данных, colTaskUpdates – это наша коллекция.

Теперь при нажатии кнопки «Сохранить», наши изменения сохраняются в источник данных, таким образом основная задача – редактирование данных в форме Power Apps – выполнена.

Однако данного набора функций будет недостаточно, для удобного использования нашего приложения. Так, например, мы можем создать индикатор, того что у нас есть несохраненные изменения. Для этого выберем подходящий значок из вкладки «Вставка», в «Значки»

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

Для которого в свойствах Visible мы прописали:

ThisItem.’***’ in colTaskUpdates.***

Также в шапке мы добавили такой же значок и текст

где в свойствах Visible этого значка и текста «У вас есть несохраненные изменения» мы прописали:

CountRows(colTaskUpdates)>0

где colTaskUpdates – это название нашей коллекции.

После нажатия кнопки «Сохранить», эти значки и текст исчезнут.

Теперь давайте добавим кнопку «Просмотр / Редактирование», которая будет отвечать за режим просмотра и редактирования формы. Аналогично как в предыдущих шагах, мы добавляем кнопку и переименовываем её, в событие «OnSelect» напишем код

Для переименования в свойствах Text пишем

Теперь выделим нужные нам элементы и изменим их свойства DisplayMode

Для значка «Удалить» в свойствах Visible укажем код для привязки к переменной «varGridEditable»

Последним шагом добавим кнопку сброса изменений, она понадобится если пользователь решил сбросить все произведенные им изменения в форме. Для этого также добавим значок сброса из вкладки «Вставка», и изменим событие «OnSelect»

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

Для этого добавим «Переключатель» на вкладки «Вставка»

Определим для него событие при изменении «OnChange»

Выделим нашу коллекцию, внутри которой находятся строки нашей таблицы

В свойствах Items этой коллекции оставим в нашем случае:

Где data555 – это источник данных

Направление — название столбца

“” – означает пустую строку, т.е. мы фильтруем по пустым значение в указанных столбцах.

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

На рисунке ниже показан конечный вид получившейся формы для маппинга данных в режиме просмотра:

И в режиме редактирования: