Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Power Query Merge based on conditions

Hi everyone,   I am trying to merge two tables, and I think I need to do the merge based on conditions.  The first table is a list of all finished inventory, which includes the end customer name, c...
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
2 years ago
let
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVnDO11PSUXJJzStLLQIynP2BRLihUqwOsrRTfmlOCpK8EVjexdUNKp+YkwMnw43Bku4eniiS/h4Ig9Hlwh2hhsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, City = _t, State = _t, Warehouse = _t]),
    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXLKKU1VSMrMTgWyHZ2cFZzz9YAsl9S8stQiIMPZXylWB6zYFMgLSk3BprakKD+zBMjy9YQpNgPy3ItSU/NgyiMio6DK/VLLFSLzi7JBzEiYenM0l7h7eEKVO+eklqXmJOalANn+HjD1FqiOcXF1gyoPSazMyS9CcYslulsQhqO5JRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial No" = _t, Product = _t, Customer = _t, City = _t, State = _t]),
    Custom1=let a=Table.Buffer(Source1) in Table.AddColumn(Source2,"Warehouse",each let w1=a{[Customer=[Customer],State=[State],City=[City]]}?[Warehouse]?,w2=a{[Customer=[Customer],State=[State],City="all"]}?[Warehouse]?,w3=a{[Customer=[Customer],State="all",City="all"]}?[Warehouse]? in w1??w2??w3)
in
    Custom1