Hits: 19762

Probably every one of us has encountered a situation, where he had to solve a problem he had already solved once. But where is the query or the PBI solution with this query??
Wouldn’t it be great to find a way to reuse your queries in Power BI Desktop or Excel?

Copy and paste

There are different ways to achieve this. One might

– Copy the M code from the Advanced Editor and paste it into a new query

or

– Copy and paste the query in Power BI Desktop or Excel.
This works pretty straight forward as you can see in the gif.

Copy und Paste Queries

Drawbacks of copy and paste

The disadvantage of both solutions is changing the code. If you want to change a part of the query, you have to admit the changes to two or more queries.

What we need is a solution, where we only have to change once a single query and all adjustments are applied in all other queries too.

The answer is – yes there is a solution for this, but it doesn’t happen automatically…. Let me show you how such a solution could be achieved.

Reuse your queries in Power BI

First, we copy the M Code from the original query and save it in a txt file.

Then we delete the old query (just for demo purposes) and replace it with a new one. In the Advanced Editor we type the following code:

let
Source = Text.FromBinary(File.Contents("C:\Users\xxxx\Desktop\Test_SharedQuery.txt")),
EvaluatedExpression = Expression.Evaluate(Source, #shared)
in
EvaluatedExpression

Apply the changes and Power Query loads the data. This works fine both in Excel and Power BI Desktop.

Take a look at the gif.

SharedQuery

Adjusting the shared query

If we have to adjust the code, we only need to change it in the txt file.
I changed the code in the txt file as follows:

let
Quelle = Access.Database(File.Contents("C:\Users\xxxx\Documents\01_oneDrive\06 Homepage\PowerBI-pro.com\Datasets\AdventureWorks.accdb"), [CreateNavigationProperties=true]),

_HumanResources_Employee = Quelle{[Schema="",Item="HumanResources_Employee"]}[Data],

#”OtherRemovedColumns” = Table.SelectColumns(_HumanResources_Employee,{“EmployeeID”, “NationalIDNumber”, “ContactID”, “LoginID”, “ManagerID”, “Title”, “BirthDate”})
in
#”OtherRemovedColumns”

By refreshing the solution in Power BI Desktop (or Excel), the adjustments are applied and work as desired.

Shared_Query_addjusted

Conclusion

The method shown allows us to share and edit queries without recoding every Power BI or Excel solution.

Possible use cases could be time/date dimensions or fact tables, which you use in different solutions.

Last point – one thing to consider: after refreshing the pbix File, you need to redeploy it to Power BI Service, otherwise the changes wouldn’t be applied in PBI Service.

Update

ImkeF (http://www.thebiccountant.com) just gave me the hint, that Chris Webb already blogged this solution in 2014. I’m three years too late…..

https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

Happy Querying!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: