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.
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.
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.
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!
