Merge 2 Queries To Certain Step

Power Query
1

Hi All,

Wondering if anyone can assist. I have 2 queries (Source & Destination).

I want to Left Join Destination to Source, however I want to merge upto a certain step in the Source to avoid breaking the Query Fold in the destination. Just wanted to know if this is possible and how would I go about doing this?

Thanks

2

Hi Ashton,
you want to avoid breaking the query fold.
=> Here is a minimal invasive approach for you:
You can reference steps in another query with Metadata.

Add a metadata record after the final step of the query:
AddedMetadata = FinalStep meta [RefName= StepName]

You can reference to it like this:
= Value.Metadata(QueryName)[RefName]

LinkedIn: Reference Steps in another Query with Metadata!

Regards,
Matthias

1 Like
3

Hi @Matthias ,

Thanks for posting this. This is exactly what I am after. Having downloaded the sample file and using the suggested method to join the tables (I created an index in both tables)

= Table.NestedJoin(Source, {"Index"}, Value.Metadata(Staging)[ReferenceStepIntermediate], {"Index"}, "Staging", JoinKind.LeftOuter)

It works without any issue.

However when i translate this to my model (Dataflow) i keep getting an error:

image

Source Query ‘CT’ has this step:

#"Expanded Table"

meta [ReferenceStepIntermediate = #"Sample Step"]

Destination Query has this step:

Table.NestedJoin(#"Renamed columns", {"ID"}, Value.Metadata(CT)[ReferenceStepIntermediate], {"ID"}, "CT", JoinKind.LeftOuter)
4

Hi Ashton,
it works fine for your usecase, but unfortunately it doesn’t work in Dataflow. :unamused:
That is a real pitty.
Regards,
Matthias

5

Hi @Matthias,

I ran some tests this morning using sample data online and the function definitely works in the Dataflow.

I then reviewed my query and after going each step I got it work. For some reason which I am not clear yet, it had something to do with a Referenced Query. Having changed the steps in the Referenced Query seemed to do the trick.

Thanks for the help and post on linkedin.

1 Like