Forum Discussion
Conditional Query Merge
Hi Anonymous ,
There is a way to do this but it's fairly complex if you're not comfortable with M code, and would also require a significant amount of memory as it involves buffering one table to compare to the other.
To your point: no, only conditionally merging some values will not give you any processing gains, as every single row needs to be evaluated regardless.
On balance, I think just doing the full merge then doing a conditional Replace Values afterwards would be the most prudent method.
Pete
Actually it's not complex at all if you just think it through! You just need to duplicate then separate the first table into a's and everything else, and then join on a to the lookup table, then append that to the table that's not = a.
First step named TheAs:
= Table.SelectRows (Source, each [Column1] = "a")
next step named TheRest:
=Table.SelectRows(Source, each [Column1] <> "a")
Add you constants value column to TheRest, call this step Table2:
= Table.AddColumn(TheRest, each "Constant Value")
Then make a new step named Table1A:
= Table.Join(TheAs, {"Column1"}, LookupTable, {"Column1"}, JoinKind.Inner)
Expand the table columns that you need. If you need to rename columns or change types, do that now. Columns in TheRest and TheAs have to have the same number of columns, and be of matching column types. So whenever you have your table with the a's looking how you want it, and let's say that the current final step is named FinalStep. Now all you have to do is reach back into your steps and append FinalStep to Table2:
= Table.Combine({FinalTable, Table2})
And you are done. The thing to remember that makes it easy is that the steps in Power Query can refer to each other in any order, and every step name can be used like a table in your functions (except for circular references).
--Nate
Expand the first table that was joined, and expand whichever