Онлайн университет современного образования без отрыва от работы

Подключение Power Query к модели данных Excel Power Pivot

Мы можем подключить практически любой источник данных в Power Query, но модель данных PowerPivot не включена в этот обширный список источников.
С помощью потрясающего DAX Studio мы можем сделать это (хотя, на наш взгляд, это все еще неудобно и сложно) по крайней мере локально - из той же книги Excel или из Power BI Desktop.

Все, что вам нужно, это открыть вашу книгу Excel, запустить надстройку DAX Studio и подключить ее к этой книге. Затем вы можете просто подключиться к модели PowerPivot, как к кубу служб аналитики SQL Server.
Но это недокументированная и крайне ограниченная функция, не поддерживаемая Microsoft, которую можно использовать только на свой страх и риск.

Шаг 1: Старт
Во-первых, нам нужен Источник. Другими словами, книга Excel с некоторыми данными, загруженными в модель данных PowerPivot. Если вы видите таблицу в окне PowerPivot, это хорошо.

У нас есть рабочая книга с этими образцами таблиц:

Исходные таблицы загружены в модель данных

Которые формируют эту простую модель данных:

Модель данных со связями, вычисляемым столбцом и мерой

В таблице «Продажи» мы уже создали вычисляемый столбец для расчета совокупной суммы для продукта по дате:

Только что рассчитанный столбец

И простая мера:

Sum of Sales := SUM(Sales[Value])

Источником таблиц в модели PowerPivot являются таблицы из этой книги, но они также могут быть из Power Query.

Во-вторых, нам нужна надстройка DAX Studio для Excel. Если вы работаете с Power BI или PowerPivot и у вас не установлена DAX Studio, у нас для вас плохие новости... Если это так, или если вы не установили надстройку для Excel, перейдите на https://daxstudio.org, нажмите зеленую кнопку, чтобы загрузить последнюю версию и установить ее. Во время установки не забудьте установить не только приложение, но и надстройку Excel. Это необходимо.

Шаг 2
Чтобы подключить Power Query к вашей модели данных рабочей книги, откройте рабочую книгу. По некоторым причинам мы настаиваем на том, чтобы была открыта только одна рабочая книга! Если одновременно открыть несколько рабочих книг, могут возникнуть проблемы.

Запустите надстройку DAX Studio из группы лент «Надстройки» в Excel. После запуска DAX Studio вы увидите окно подключения, где вы должны выбрать первый вариант:

Подключите DAX Studio к файлу Excel

Нажмите «Подключиться». После того, как соединение установлено, не закрывайте DAX Studio, программа должна быть открыта.
Теперь все готово для подключения.

Шаг 3
Теперь перейдите к Power Query и подключитесь к «базе данных служб аналитики SQL Server (импорт)».

Для этого нам нужно знать адрес сервера. Если бы это подключение было к какому-либо серверу или Power BI Desktop, мы могли бы увидеть его адрес в правом нижнем углу окна DAX Studio:

Адрес открытого файла PBIX в DAX Studio

Но при подключении к файлу Excel мы видим только это:

Нет сервера: адрес порта для файла Excel!

Теперь мы раскрываем главный секрет:
Вам необходимо использовать http://localhost:9000/xmla в качестве имени/адреса сервера. Вот и все.

Конечно, вы также можете использовать это имя базы данных (необходимо записать его, если вы хотите использовать пользовательский запрос MDX или DAX в соединении): «Microsoft_SQLServer_AnalysisServices».

Варианты подключения

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

Используйте вашу текущую аутентификацию Windows для подключения

Ура!

Power Query подключен к модели данных PowerPivot как к кубу

Теперь вы видите модель, все ее вычисляемые столбцы и показатели как куб OLAP. Из него вы можете создавать любые нужные вам таблицы.

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

Читать модель PowerPivot из Power BI

Если по какой-либо причине вы не хотите импортировать модель данных Excel в Power BI, вы можете использовать этот прием для подключения к PowerPivot в Power BI Desktop. Но опять же, только в режиме импорта.

Power BI может подключаться к модели PowerPivot только в режиме импорта

Все шаги одинаковы.
Для нас этот трюк - один из длинного списка «что мы можем». Если честно, он один из самых полезных для этой задачи. Да, он сложен и ограничен ручным обновлением. Но если вам необходимо срочно извлечь некоторые данные из модели данных Excel в PQ, вы можете осторожно использовать этот подход.

Например, вам может потребоваться выполнить некоторые операции по слиянию данных в Power Query, но некоторые из них слишком сложны в Power Query и имеют очень плохую производительность (обычно речь идет о мэшапах, которые требуют много операций сканирования одной и той же таблицы) или есть другие причины для выполнения некоторых вычислений в DAX, а не в M. Вы можете экспортировать промежуточные результаты в модель данных и выполнить необходимые вычисления в PowerPivot, а затем снова прочитать их в Power Query.

Наши курсы по Power BI:
Курс Аналитик BI
Курс DAX Mastering
Курс Финансовый анализ в Power BI

Наши каналы:
Facebook
Instagram
VK
Telegram
Полезные статьи