how to refer a step in another query ?

[Anonymous]

Hello,

I have several queries that contains several steps each.

In a query B, can I refer to the result of a step called STEP NUMBER 4 in another query called let's say A ?

If yes, how can I access it ?

And is it mandatory to have query A that is activated for loading and/or included in the report update ?

[ImkeF]

Hi @Anonymous ,
short answer: Never use #shared in production. It won't refresh in the service. It is an interesting function to play with, but nothing to use for production purposes.

I like the goodly-solution very much.
There is also a variation to it here in the comments on LinkedIn which allows you to keep the code of your existing subsequent queries: https://www.linkedin.com/feed/update/urn:li:activity:7029530911066968066?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A7029530911066968066%2C7029567762314223616%29

[ImkeF]

Hi @Anonymous ,
yes, you are basically overwriting the existing metadata.
Instead you can combine it into one record like so:

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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Intermediate Step1" = Table.TransformColumns(#"Changed Type", {{"Column1", each _ * 2, type number}}),
    #"Intermediate Step2" = Table.TransformColumns(#"Intermediate Step1", {{"Column1", each _ * 3, type number}}),
    #"Final Step" = Table.TransformColumns(#"Intermediate Step2", {{"Column1", each _ * 4, type number}}),
    #"Add Metadata with Intermediate Steps" = #"Final Step" meta [ReferenceStepIntermediate1 = #"Intermediate Step1", ReferenceStepIntermediate2 = #"Intermediate Step2"],

in
    #"Add Metadata with Intermediate Steps"

[ImkeF]

Hi @Anonymous ,
you could do it like described here:
Reference an intermediate step from a different query in Power Query (thebiccountant.com)

[Anonymous]

Thank you for the solution !

I have tried the example shown in the website and wanted to check if we could store more than one intermediate step per query but it gives me an error when trying to access :

= Value.Metadata(Staging)[ReferenceStepIntermediate1]

 Is it me doing it wrong or it is not possible, or doesn it need to be done in a different way ?

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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Intermediate Step1" = Table.TransformColumns(#"Changed Type", {{"Column1", each _ * 2, type number}}),
    #"Intermediate Step2" = Table.TransformColumns(#"Intermediate Step1", {{"Column1", each _ * 3, type number}}),
    #"Final Step" = Table.TransformColumns(#"Intermediate Step2", {{"Column1", each _ * 4, type number}}),
    #"Add Metadata with Intermediate Step1" = #"Final Step" meta [ReferenceStepIntermediate1 = #"Intermediate Step1"],
    #"Add Metadata with Intermediate Step2" = #"Final Step" meta [ReferenceStepIntermediate2 = #"Intermediate Step2"]
in
    #"Add Metadata with Intermediate Step2"

[Anonymous]

It seems like another solution would be that one : https://goodly.co.in/extract-any-step-power-query/

From what I see it should allow us to refer to as many steps from the query as we want.

What do you think @ImkeF of the impact of this solution though (removing the "let .. in") ?

[Anonymous]

@ImkeF  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

[smpa01]

@ImkeF  this is super cool !!!