Forum Discussion
Community ChampionWays to share Power Query queries between PBI, Excel, SSAS: Copy/paste, templates, Data Catalog?
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:
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:
6 Replies
- MarcelBeug
I think this is also related to information from ImkeF on:
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.
- 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.
