Можно ли выгрузить данные из Power BI в Google Sheets? Этот вопрос мне прислали в Telegram и он поставил меня в тупик. Я знал, что есть mez-коннектор для подключения к Power BI Service, также есть разные виды подключения к Power BI из Excel, но в Google Sheets - не слышал.
Пришлось собрать все материалы воедино, победить нестандартную авторизацию и написать нормальные функции для Excel, Power BI и Google Sheets. Теперь можно получать данные датасета Power BI Service с помощью DAX-запроса в Excel, Power BI или Google Sheets.
Настройка API Power BI
Часто нам достаточно получения токена, но в Power BI настройка API не так проста. Чтобы не ссылаться на чужие статьи, распишу её целиком.
Переходим по ссылке и регистрируем приложение (внизу будет кнопка "зарегистрировать").
Находим приложение в списке приложений и нажимаем на него. Видим ID приложения и каталога - они нам понадобятся:
Слева будет меню, где вам нужно найти "Разрешения API", чтобы добавить разрешение:
Выбираем Power BI Service и выбираем следующие настройки:
Предоставляем согласие администратора и ваше API готово к работе:
Добавляем секрет клиента, можно поставить ему желаемый срок действия:
Нам потребуется не ID секрета, а именно его значение:
Переходим в портал администрирования Power BI и активируем возможность работы с API:
Интересный момент: получить данные по API могут только пользователи Power BI Pro. Дело в том, что для предоставления доступа к API нужно дать приложению доступ к рабочей области. Но стандартная "Моя рабочая область" не имеет функционала предоставления доступа. Считается, что это личная рабочая область, а создание других рабочих областей - это уже функционал Pro.
Когда мы создали рабочую область, заходим в управление доступом к ней и жмём "Добавить людей или группы":
Вместо людей находим приложение и даём ему доступ к рабочей области:
Наборы данных и запрос DAX
Мы создали приложение в каталоге Azure и добавили для этого приложения "секрет клиента". Помимо ID каталога, ID приложения и Секрета клиента, мы должны знать ID набора данных (датасета) и иметь DAX запрос для получения нужных данных.
Анонсы всех видео, статей и полезностей - в нашем Telegram-канале🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
Для получения ID датасета заходим в Power BI Service, вводим в поиск название файла и находим значок с точками. Это и будет датасет или как он сейчас называется в Power BI Service - "Семантическая модель", жмём на его название:
Перейдя в набор данных, обнаруживаем его ID после "datasets/" и до "/details":
Остаётся придумать запрос DAX. Если вам нужна вся таблица, самым простым вариантом будет:
EVALUATE 'Название таблицы'По аналогии с DAX Studio, здесь нужно использовать EVALUATE, чтобы вывести табличный результат. Если нужно что-то более сложное - заранее проверьте работу вашего DAX-запроса в Power BI Desktop. Не забывайте про кавычки, названия таблиц на русском языке или с пробелами не работают без кавычек.
Теперь мы знаем: ID каталога, ID приложения, Секрет клиента, ID датасета и запрос DAX. Можно переходить к получению данных🔥
Подключение к API Power BI из Power Query
Копируем функцию для PQ и вставляем в расширенный редактор Power Query в Excel или Power BI:
Вызов функции выглядит так - результат приходит в виде строк, которые нужно развернуть:
При использовании функции в Power BI, отчёт будет корректно обновляться.
Подключение к API Power BI из Google Sheets
Мы разместили коннектор для Google Sheets на Boosty по подписке ПРО. Копируем функцию, заходим в Google Sheets, выбираем "Расширения" -> "App Script":
Вставляем скрипт, указываем в первых строчках основные параметры и сохраняемся. Запрос DAX может меняться, поэтому его мы в коде не прописываем, всё остальное прописывается именно тут. client_id - это ID приложения, остальное соответствует русским названиям:
После сохранения скрипта обновляем страницу Google Sheets, в верхнем меню должен появиться пункт "Power BI". Выбираем любую ячейку и пишем туда DAX-запрос. Затем вызываем функцию "Execute DAX" из появившегося пункта меню.
Чтобы структурировать страницу, скрипт установит запрос DAX в левую верхнюю ячейку, а ниже разместит результат. Если вы захотите обновить таблицу, выберите ячейку с запросом и повторите вызов. Функция работает именно с выбранной ячейкой, будьте внимательны.
Итоги
Теперь у вас есть функции для получения данных из Power BI Rest API в Google Sheets, в Excel или в новый отчёт Power BI. Это позволит создавать отчёты для проверки других отчётов и использовать данные разных отчётов вместе. Если вам помогла статья, можете отблагодарить подпиской на Boosty😊
Отдельное спасибо коллегам из Prometriki и Роману Шапкову - их материалы очень помогли в работе над этой статьей:
- https://github.com/RomanShapkov/ExecDAX - mez коннектор Романа, который был взят за основу,
- https://prometriki.ru/power-bi-rest-api-podklyuchenie-i-poluchenie-dannyh/ - статья, которая помогла быстро разобраться с настройкой API.
