When you reference another query in Power Query you will automatically get the results of the final step. But what if you want to reference a step that sits within that referenced query?
One solution would be to split up that query into 2 and reference the final result of the splitted query. But that could result in additional query time, as the data might be pulled from the source twice (once for each query).
A potentially better alternative is to add the result of the intermediate step as metadata of the query itself. That way you could pick steps just as you need them.
To do so, you add a metadata record to the final step of the query. If you need just one intermediate step, it could look like so:
Using the “meta” expression does the trick here: You reference the previous step (“Final Step”), add the meta keyword and then add the record containing the referenced step (“Intermediate Step”). The name of the record field “ReferenceStepIntermediate” is what you need to address the value from a different query:
This is an unintrusive way to make intermediate steps available for other queries without breaking the logic that exists in your solution so far.
Please check the file attached to follow the steps: Reference-Step-in-another-Query.pbix
Enjoy and stay queryious 😉
Edit: There are nice other ways to this as well:
https://goodly.co.in/extract-any-step-power-query/
Holy crap you have got to be kidding me. Pure genius!!!
Very clever! One thing to test is if this could open up the possibility of unintended circular references if, for example, you wanted to do further processing on an intermediate step in another query and then join or append that query to the original query where you added the metadata, but perhaps lazy evaluation will keep us in the clear 🙂
Hi Alex,
good point – haven’t thought about that yet!
You gave some didactic example, for… one column, and… one row ! Real data will almost never have only one column, and only one row ! People will need to know how to get a certain value from a certain external query, from a certain column, and also from a certain row, perhaps. These are the reasons why I find your example really poor, not aligned with real world data structure.
there could have been 10 columns instead of 1 and the result would have been the same. If you reference the step, you will retrieve the records as shown in that step. I just tried using this in a merge and it worked perfectly:
#”Merged queries” = Table.NestedJoin(Navigation, {“Document_No”}, Value.Metadata(Sales_QuoteArchiveHeader)[ReferenceStepIntermediate], {“No”}, “Sales_QuoteArchiveHeader”, JoinKind.LeftOuter)
Navigation = previous step in current query
Sales_QuoteArchiveHeader = query that contains step to merge with
[ReferenceStepIntermediate] = step to merge with
:/ Please, give us an example with two references (from 2nd and 3rd query to the first, which is with intermediate steps) to two intermediate steps located in one query.
1st reference to to simple get data
2nd to NestedJoin.
And there will be no need for this code thread and words…
Yeah well then provide us with such an example, Romulus!
Hello, I have Power BI Desktop Version : 2.109.782.0 64-bit (septembre 2022), so pretty recent, but when I try to run your example, it says it is impossible to open the document and that the queries have been created with a new version of Power BI Desktop and might not work with my version. Do you know if this “meta” thing is an extremely new feature ?
I might be wrong on this, but since this article is geared towards Power Query (Excel), it’s entirely possible this isn’t supported in Power BI.
Have you seen Goodly’s trick to reference any step in a Power Query:
https://goodly.co.in/extract-any-step-power-query/
Thanks – that’s very cool. Have put the link in the article above as well.
Imke, I’m so flashed
It is also interesting to define multiple metadata sets one after another:
FinalStep = Table.TransformColumns(IntermediateStep, {{“Column1″, each _ * 3, type number}}),
#”Add Metadata with Intermediate Step” = FinalStep meta [ReferenceStepIntermediate = IntermediateStep],
#”Add Metadata with Source” = #”Add Metadata with Intermediate Step” meta [RefSource = Source]
Or writing multiple fields in the record:
MultipleMetadata = FinalStep meta [RefSource = Source, RefInt = IntermediateStep]
Thanks for sharing this
I was wondering about how to retrieve several intermediate steps. I’ve tried your 2 solutions and it works, thank you @Imke Feldmann for the initial solution and @Melanie Breden fore the improvement of it.
Hi Melanie,
very pleased to hear 🙂
There are other cool solutions to this a well that I have added at the end of my blogpost as well.
You will probably like them as well.
Cheers, Imke
Still don’t know how to launch it :/ What should i write in second query to get data from intermediate step2 ?
Can you, please, upload a simple example with 2 references to two intermediate steps?
I’ve tried my luck asking chatgpt how to do it and it came up with an interesting solution :
SourceOfQueryB = #shared(QueryA)[StepNumber4]
It is not working yet on my PC but maybe it is due to a version of my Power BI Desktop, I have just downloaded version “2.112.1161.0 64-bit (décembre 2022)” though.
Here is the code of my 3 queries :
– 1st query Staging2Init :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlSKjQUA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{“Column1”, Int64.Type}})
in
ChangedType
– 2nd query Staging2 :
let
Source = Staging2Init,
IntermediateStep1 = Table.TransformColumns(Source, {{“Column1”, each _ * 2, type number}}),
IntermediateStep2 = Table.TransformColumns(IntermediateStep1, {{“Column1”, each _ * 3, type number}}),
FinalStep = Table.TransformColumns(IntermediateStep2, {{“Column1”, each _ * 4, type number}})
in
FinalStep
– 3rd query Loading2 :
let
Source = #shared(Staging2),
Personnalisé1 = Source[IntermediateStep1]
in
Personnalisé1
I get an error on line “Source = #shared(Staging2),” :
Formula.Firewall: Query ‘Loading2’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
I thought the fact that I divided my original query into Staging2Init and Staging2 would prevent this error but it doesn’t.
When I further asked chatgpt how to solve that, it told me that IntermediateStep1 should be set as a shared table, pointing me to a menu Table on the toolbar, then a menu Shared Tables, then Create a shared table, but I can’t find it.
Does it ring a bell to use its suggested solution with the #shared keyword ? And if yes, how can I solve my error message here please ?
Thanks in advance
Your example doesn’t work because you didn’t specify metadata in Staging2.
//2nd query Staging2 :
let
Source = Staging2Init,
IntermediateStep1 = Table.TransformColumns(Source, {{“Column1”, each _ * 2, type number}}),
IntermediateStep2 = Table.TransformColumns(IntermediateStep1, {{“Column1”, each _ * 3, type number}}),
FinalStep = Table.TransformColumns(IntermediateStep2, {{“Column1”, each _ * 4, type number}}),
SetMetaData = FinalStep meta [IntStep1 = IntermediateStep1, IntStep2 = IntermediateStep2]
in
SetMetaData
Also, metadata is retrieved with the Value.Metadata function.
This code is detailed to see in the Source step what metadata is present in the query:
// 3rd query Loading2 :
let
Source = Value.Metadata( #shared[Staging2]),
Personnalisé1 = Source[IntStep1]
in
Personnalisé1
Can also be done in one step:
Personnalisé1 = Value.Metadata( #shared[Staging2] ) [IntStep1]
of course also works without #shared:
= Value.Metadata(Staging2)[IntStep1]
Hello @Melanie Breden,
What you are trying to do with your answer is to apply the solution using meta and Value.Metadata() keywords. I have tried that already as I said in my previous answer to your comment, and it works fine. What I am trying here is to achieve a similar result but using a different method because chatgpt suggested this #shared keyword / shared table. Does this #shared keyword / shared table notion exist or chatgpt is completely messed up on this ?
sorry, i just wanted to show how it works with #shared.
chatGBT has probably guessed rather than known.
So much thanks to you and your explanation! This way it works properly
Hi jmclej,
I have stopped playing with #shared years ago, because it won’t refresh in the service.
OK. I understand it doesn’t refresh automatically but it refreshes if we update the datasources parameters
Hi Imke,
unfortunately, reference of this type doesn’t prevent multiple data source querying. Moreover, my tests shows that “Loading” query will perform the full (!!!) Staging query refresh and only then get the desired intermediate step.
So, if intermediate step took 1 minute and the last part took 10 minutes, using meta reference will take 11 minutes (at least).
I did these test many years ago and may be cannot recall details, but that’s why I almost never used this technique.
Hi,
Do you have an alternative to achieve the same result in a more efficient way then please ?
Hi jmclej,
There’s only two other ways to do it.
1) one mentioned by Imke in the end of this post: transform the Staging query in the record and reference it’s fields. This will execute only steps required by each branch, independent from other branches.
2) split the query in two via “Extract Previous”. It will create a new Staging query with the common steps, so you can reference it in the new branch. Other queries architecture will remain untouched. From the performance point of view it is almost the same as 1), except you can try to leverage Table.Buffer in correct places, for example, or PQ will use cached results (we cannot manage it, though)
Hi Maxim,
that’s interesting, thanks for pointing this out!
Are there proper alternatives?
I love this method. This way you can even create a single reference query with multiple transformations leading to different outcomes and all you have to do is reference the correct step. Game changer!
Does it work on Dataflow? I tried but seems it is not
You learn a new thing everyday… I used to do this by adding a outputtype parameter to a query and switching the output based on outputtype..
let
fn = (optional outputtype as text) as any =>
let
……
intermediatestep = ….
finalstep = ….
output = if outputtype = “intermediate” then intermediatestep
else finalstep
in
output
in
fn
Nice!
Extending above discussion created a video to share totally dynamic method to refer to ANY step from other query, checkout the vide here – https://youtu.be/JkNYvFoJ6HQ
Does it function with Dataflow? I attempted, but apparently it’s not
How creative man! Congrats, I never thought this could be possible, simply brilliant !
I have used this in several queries but I get error Query Folding not available. Can you help why this happening, I am using PQ in excel