Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Conditional Query Merge

Hi, i would like to merge (not merge as new) table1 with a table2 according to a conditional rule applied to another column of table1 so if table1  1 a 2 a 3 a 4 b and table2 a 15 b 7 // if...
Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    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

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

Agree with BA_Pete, the better workaround is merge queries and apply conditional rules on replace value.

Refer this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjKCs4zhLBMgK0kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, Table2, {"Name"}, "Table2", JoinKind.LeftOuter),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Queries", each [Table2], each if [Name] <> "a" then [Table2][Value]{0} else [Table2],Replacer.ReplaceValue,{"Table2"})
in
    #"Replaced Value"

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.