Importing M Power Queries from Excel 2016 into Power BI

[RafaelKnuth]

I wrote several M Power Queries in Excel, and I want to import those into Power BI.

These queries are connections only, they are not loaded as tables into my Excel workbook.

In case of a workbook, the corresponding M Power Queries get loaded into Power BI.

But I only need the M Power Queries.

Is there a workaround?

Any feedback highly appreciated. Thanks!

[Greg_Deckler]

Hmm, I can't find any way to get to the underlying "M" code for queries in Excel. If you could, then it would be a simple copy and paste into the Advanced Editor.

[RafaelKnuth]

Thanks, there are some limits to copy and paste thogh, as I want to import Queries by the dozens 😉 So, this certainly works with few Queries, but that workaround becomes hugely error-prone when juggling with many Queries at once.

[otravers]

Put your queries in a folder, then copy and paste the whole folder. I've tested this between Excel, Power BI, and Visual Studio (SSAS). See this thread and its screenshots where I show where in the UI you can do it:

https://community.powerbi.com/t5/Desktop/Ways-to-share-Power-Query-queries-between-PBI-Excel-SSAS-Co...

[RafaelKnuth]

Thanks! One dumb question though: How do I create a folder for my queries in Excel?

I wasn't able to figure that out by myself.

(I am totally new to M ... and I didn't know much more than how to use the SUM function until recently)

[otravers]

@RafaelKnuth In the Query Editor, right click on the Queries left panel and select "New Group...", as per the screenshot below:

[RafaelKnuth]

Hi,

so, here's what I did step by step:

1. Create new group in Excel

2. Load query group into Power BI

3. But what I see then is this

I am not qute sure what to do with this. Can you advise? Thank you so much for your effort!

[otravers]

Looks like you loaded the spreadsheet from Get Data? That's an entirely different workflow. What I was suggesting is to just copy and paste your query group from one Query Editor to the other. Right click on "Beginner Queries" in the Excel query editor and select Copy. Then right click in the query pane in Power BI and select Paste,

[RafaelKnuth]

My bad, thanks! Got it now. It works.

But there is no way to share queries with other users, say, if I wanted to share my queries with you or vice versa?

The solution you described only works on one and the same system.

[otravers]

Correct, what I gave you is a single-user desktop shortcut.

You can share queries in the Azure data catalog from Excel, but that's not supported as a query source directly from Power BI Desktop, even though you need a Power BI Pro license to post queries from Excel to Azure Data Catalog!

Excel 2016 screenshot:

But then in Azure Data Catalog the "Open In" menu is greyed out:

Normally it should list Power BI Desktop like in the example below (which is a SQL source, not a PQ query):

Frankly the whole experience is very confusing, I've found ADC very slow, and it feels like they stopped development in the middle of doing it. Feels like a poor handover from the Office team to the Azure team that can only make sense to insiders within the involved teams.

See this thread:

https://community.powerbi.com/t5/Desktop/Ways-to-share-Power-Query-queries-between-PBI-Excel-SSAS-Co...

And this thread:

https://community.powerbi.com/t5/Desktop/Data-catalog-integration-with-Power-BI/td-p/235160