Ways to share Power Query queries between PBI, Excel, SSAS: Copy/paste, templates, Data Catalog?

[otravers]

Now that Power Query and M work across Excel, Power BI and SSAS, I'd like to discuss the various ways to organize your queries if you intend to share them across tools. While creating proofs of concepts or pilots, and even for some production work, I like the idea of being able to go as seamlessly as possible between the three environments.

First, one thing that I really like is the fact we can copy one or several queries back and forth between Excel and Power BI, and that pasting a query will also paste along its dependencies (i.e. functions, parameters). The good news is that you can also do that to the query editor in SSDT (screenshots below). [I edited this part of my post, somehow I hadn't found how to do this in SSDT yesterday.]

Second, efficient copying/pasting is great, but how would you go about maintaining a single source for your queries?

As far as I can see Azure Data Catalog is still not directly integrated from the PBI Desktop UI, though you can open in PBI Desktop, Excel, and SSDT from the Azure portal:

https://docs.microsoft.com/en-us/azure/data-catalog/data-catalog-get-started

I just tested this feature and it works well in all three cases. With Excel, it's creating an ODC file but these are not supported in PBI:

https://blog.crossjoin.co.uk/2017/06/04/exporting-power-querym-queries-to-odc-files-in-excel-2016-ge...

Also, in the Azure data catalog I didn't see an obvious way to import a whole bundle of PQ queries/parameters/functions. I work with REST APIs that require query parameters, paging etc., it's not just about saving a couple SQL connection parameters.

Finally, Data Catalog Search under Get Data in Excel 2016 seems to still be looking for the old Power BI Data Catalog, while this was supposed to have been replaced by Azure last spring, as per:

https://community.powerbi.com/t5/Desktop/Azure-Data-Catalog-Integration/td-p/562

Frankly, the whole thing looks like an unfinished mess. Am I missing something? How are you guys dealing with centralizing and managing complex sets of PQ queries with their dependencies? Is there a smart workaround to maybe put everything in a pbit format and then somehow sync its queries with Excel and/or SSAS?

Just to be clear, this is about the PQ part only, I'm *not* talking about importing a whole data model as described in the following link:

http://biinsight.com/import-power-bi-desktop-model-ssas-tabular-2016/#important-updates

For reference, copy/paste works great between Excel, PBI, and SSDT query editors:

 

 

[ImkeF]

I currently keep them in a record: http://www.thebiccountant.com/2017/08/27/how-to-create-and-use-r-function-library-in-power-bi/ . That solves the problem with the dependencies, as all functions in the record will become part of the environment at once and can be referenced between each other. Dragging the code from GitHub or other sources and consolidating it using Power Query to create one large record.

But that's not ideal and I would very much prefer to use an extension file instead: http://www.thebiccountant.com/2017/10/06/create-a-function-library-in-power-bi-using-m-extensions/

Unfortunately this is currently only supported in PowerBI Desktop.

[otravers]

Some people use Data Connectors to consolidate their PQ functions, I just saw this related feature suggestion from the Ideas forum: Custom Connector as a Portable Library of Functions

And these Github repos:

• Extension Functions for Power BI Desktop and Excel
• pquery
• PowerQueryExtensions (a superset of the above)

So you load your M functions in PBI/Excel via Get Data, which works for your average user to consume the functions, but creating your own Data Connector is a bit hardcore for this purpose.

[ImkeF]

Hi @otravers,

which advantages of custom connectors do you see in comparison to extensions to host/share PQ functions?

[otravers]

Hi ImkeF, I didn't mean to imply one method was better than the other (behind the scenes extensions are pretty close to custom connectors anyway), I just posted about custom connectors to document the various ways people have been managing their PQ functions so far. I could see how loading a connector through Get Data would be a bit more friendly than dealing with .mez files behind the scenes and having to restart PBI Desktop, but this is minor.

I could try extensions as you're suggesting on your blog, but for my use case that will be of limited value because I often go back and forth between Excel and Power BI. Hopefully Excel support will be added soon, from your post it sounded like this was on the roadmap.

On Github they state that "We are currently working on enabling a central marketplace/distribution mechanism for Data Connectors." We'll see what that looks like and if it only supports public connectors (like getting custom charts from the Office Store / AppSource) or also private connector/function catalogs.

[ImkeF]

Currently we cannot use custom connectors in Excel as well.

Extensions use .mez-files just the same way like custom connectors: You have to have them on your local machine and once you change them or add a new one, you have to restart PBID.

[MarcelBeug]

I think this is also related to information from @ImkeF on:

http://www.thebiccountant.com/2017/10/15/bulk-extracting-power-query-m-code-from-multiple-pbix-files...

Otherwise I don't have much experience with this subject.

One remark from my side: Power Query in Excel does interact with the current Excel workbook (input/output from/to Excel sheets/ranges/tables), something that is not possible in other environments, e.g. you can't use function Excel.CurrentWorkbook in a Power BI Desktop file.