How to load a PowerBI symantic model as source for Power Query in Excel

[timothius]

Dears, hope you can help me with the following. I am trying to do some data transformations in Excel Power Query on data that is from an existing connection that I got through: Data - Get Data - From Power Platform - From PowerBI. 

How do I get this connection to show up in Power Query as a source? When I select new source there in Power Query I do not have the 'From PowerBI' option anymore.

Thanks!

[ricsan]

I can connect to a Power BI semantic model in import mode from Excel through Get Data, From Database, From SQL Server Analysis Services Database (Import).

Some notes:

• In Server prompt, enter the workspace's connection link as shown in your workspace settings (i.e. powerbi://api.powerbi.com/v1.0/myorg/aWorkspaceName).
• In Database, enter the name of the semantic model you want to connect to, or select one from Navigator in the next steps.
• For access, sign in with Microsoft account instead of Windows or Basic.
• Just in case these are factors for a successful connection, I have a Pro license in a workspace with Premium capacity and XMLA read/write enabled.

[Anonymous]

Hi @timothius ,

If you can't find "From PowerBI" option in your Excel, it is possible that your tenant's administrator has turned off the relevant tenant setting, please contact your tenant's administrator to enable the setting for you.

If your tenant administrator has turned on the settings, check to see if you have Build access to the semantic model in Power BI? Or are you in the Contributor role and above in the workspace?

You must also have a Fabric license to use this feature.

In addition to this, I would also recommend that you check the version of Excel. Your version of Excel Desktop must be an updated version of Excel for the web.

All of the above suggestions can be found in this document, please follow the document to troubleshoot the cause yourself, thank you!
Power BI semantic model experience in Excel - Power BI | Microsoft Learn

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[timothius]

Thanks @Anonymous for the comprehensive explanation. 

The thing that strikes me is that I am able to (in the same file) add a live connection to the semantic model I need and create a pivot as per below:

However in the same file, I can't create a source on PowerQuery, to that semantic model as the option for PowerBI is not there. 

Maybe I am doing something wrong? Appreciate your time and effort here!

[Henrycl]

Hello!

I've been using a work-around, it is not the most efficient, but it works.  Follow the steps you mentioned before for creating a pivot table, but select "insert table" instead of "insert pivottable" and select all the fields from the table you want, and insert it, it will create a new tab with the full table in your excel file, then  go back to the Data tab, and press "Get Data", "From Table/ Range" and select the table that you just inserted, and you will be able to manipulate it in power query.  Updating is a little tricky, you need to open the "Queries & connections" tab, select "connections" and update the connection to the semantic model first, then click on "queries" and you will see your power query, you can refresh It there. 

[Anonymous · Accepted solution]

Hi @timothius ,

As you said, connecting to the semantic model is Live connection mode, and Power Query is not supported in Live connection mode. There is no other mode to connect to a semantic model except Live connection mode, so it is true that you can't connect to a semantic model in the Power Query Editor.
This link has a detailed list of Power Query data sources in Excel:
Power Query data sources in Excel versions - Microsoft Support

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amustafa]

In your Excel, make sure that you are signed in with your Power BI service acocunt.

or - From your Semantic Model, right click and select the Anlyse in excel option. This will open the Excel for Web. Download this excel copy to your desktop.