Analyze in Excel Dataset - How to connect to single dataset TABLE using Power Query?

[jdusek92]

Hello,

I am able to load a single Power BI dataset table using Analyze in Excel and changing the MDX query for the  Workbook Connection (not Power Query) that is generated by clicking on a value/measure in a Pivot Table connected to the dataset:

after changing the Command text to evaluate 'table' the table refreshes with the full table from the model, which is amazing.

I am looking for a way how to load this table using Power Query (in Excel), to do some more data transformation before loading to worksheet.

Can you help me with this? I believe it should be possible to get a single table from dataset using Power Query, when it is possible via legacy connections.

maybe via ?

Thank you

Jakub

[Vera_33]

Hi @jdusek92 

Yes, you can, connect in Excel or PBI, AS connector, using the navigation steps will have Cube.Transform, or you can put your DAX query in, not just Evaluate a table, more sophisticated one is fine, too.

Excel connector is the below one

Server is your workspace XLMA endpoint, you need premium capacity or PPU, database is your dataset

Source =  AnalysisServices.Database(Server, Database, [Query="EVALUATE 'yourTable'", Implementation="2.0"])

[jdusek92]

Hello @Vera_33 

I have only PRO license - but data is loading fine using the legacy workbook OLE DB connection. 

Power Query AnalysisServices.Database returns "The remote server returned an error: (401) Unauthorized."

[v-kkf-msft]

Hi @jdusek92 ,

You can open the Power Query editor in Excel and enter the URL of the PPU/Premium workspace. Then you can import the specific tables of the dataset.

You need permissions for the underlying dataset if you want to connect to Power BI datasets in Excel. You could have this permission in either of two ways:

• You could have the Member role in the workspace containing the dataset.
• You could have Build permission for the dataset in a workspace, or in an app that contains the dataset. Read more about Build permission for Power BI datasets.

And please make sure you have enabled XMLA read-write in the Admin portal.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdusek92]

Hello @v-kkf-msft  thank you for more info,

I was able to connect to my PPU trial Workspace using AnalysisServices.Database.

I am still wondering why for Power Query I need Premium Workspace, but the Analyze in Excel connection does not require a Premium. Both connect to the same data source, I suppose?

Also, I prefer to use just the PRO license - is there a way to use any Power Query connector to connect to a PRO dataset/table? Same query as I achieved by editing the legacy OLEDB query in my original post. With this in Power Query I would be able to further transform the table

Warm regard,

Jakub

[v-kkf-msft]

Hi @jdusek92 ,

When we use AnalysisServices.Database to connect Power BI workspaces, we are actually getting Tabular Object Model from Power BI XMLA Endpoint. XMLA endpoints are supported only for workspaces hosted on Premium capacity. If you will try to connect to a Pro workspace you will get this exception:

Initial catalog property is required in order to connect to Power BI Pro workspaces.

If you want to connect pro workspace datasets in excel, then you need to use the "From Power BI" function.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdusek92]

So it is not possible to recreate a connection to dataset table - that I showed in my original post, using legacy OLE DB - in Power Query?