Forum Discussion
Left Join for tables containing duplicate records
https://drive.google.com/file/d/1JJt7iQzBOJpaF0S1KZVSA16-tfhcILiY/view?usp=sharing I have a Table A which contains duplicate records, Table B which contains duplicate records. Duplicate record...
Hi Anonymous
Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFBDgMhCEXv4nqqiChwFjP3v8YIajOZJk2a9G8gHx+g9h7qUjgCNSaLmCghYB6pFlIY8eW1WPI+FpHDefSAS8PPULhExzBl2C0QmJq1uNWrjq6Gl6lhsctZ2egwfHptEAutRNXJvGTuXB9Tfa4tovLc+AaqypzYvt1XTVEcvr3We938AYvIRNky+B/58w+dFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Conc " = _t, #"Discoverer Amount" = _t, #"Default Effective Date" = _t, Difference = _t, #"AP " = _t, #"Amount on Hold" = _t]), #"Renamed Columns" = Table.RenameColumns(Source,{{"Discoverer Amount", "DA"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Conc "}, Table.Distinct(Table.RemoveColumns(#"Table B", "Amount")), {"Concat"}, "Table B", JoinKind.LeftOuter), #"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Category", "Type"}, {"Category", "Type"}) in #"Expanded Table B"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi Anonymous
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFBDgMhCEXv4nqqiChwFjP3v8YIajOZJk2a9G8gHx+g9h7qUjgCNSaLmCghYB6pFlIY8eW1WPI+FpHDefSAS8PPULhExzBl2C0QmJq1uNWrjq6Gl6lhsctZ2egwfHptEAutRNXJvGTuXB9Tfa4tovLc+AaqypzYvt1XTVEcvr3We938AYvIRNky+B/58w+dFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Conc " = _t, #"Discoverer Amount" = _t, #"Default Effective Date" = _t, Difference = _t, #"AP " = _t, #"Amount on Hold" = _t]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Discoverer Amount", "DA"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Conc "}, Table.Distinct(Table.RemoveColumns(#"Table B", "Amount")), {"Concat"}, "Table B", JoinKind.LeftOuter),
#"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Category", "Type"}, {"Category", "Type"})
in
#"Expanded Table B"
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |