Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

conditional merge queries with duplicates in column

I have two tables, and both tables share a column with a few values that are the same as another value in that column.  One of these columns (with a few values that are the same) is Municipal[municip...
Need Help
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

You can do a merge on both columns and then expand the Duplicate column.

 

 

Full M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRCskvzyvOyCxQitWJVnJxdQMKOeUX5ZemZyCJoChy9/BEVhQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Municipality = _t, #"Municipality Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Municipality", type text}, {"Municipality Type", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Municipality", "Municipality Type"}, Municipal, {"Municipality", "Duplicate"}, "Municipal", JoinKind.LeftOuter),
    #"Expanded Municipal" = Table.ExpandTableColumn(#"Merged Queries", "Municipal", {"Duplicate"}, {"Duplicate"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Municipal",{{"Municipality", Order.Ascending}, {"Municipality Type", Order.Ascending}})
in
    #"Sorted Rows"

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

AlexisOlson  Your screenshot shows the reverse order for merging queries.  Type is supposed to be the lookup table.  (The Municipal table has other columns, which my post didn't include for the sake of simplicity.)