Forum Discussion
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[municipality]. Its lookup table has the same column.
I've added a Municipality[Duplicate] column to identify the "duplicative" names in Municipal[municipality]. Note that Municipal[Duplicate] has different values for a "duplicate" in Municipal[Municipality]. So the Municipal table has the following columns:
| Municipality | Duplicate |
| ABC | null |
| DEF | Borough |
| DEF | Township |
| GHI | null |
The Type lookup table has the following columns. Note that Type[Municipality] has values like Municipal[Municipality].
| Municipality | Municipality Type |
| ABC | Township |
| DEF | Borough |
| DEF | Township |
| GHI | City |
How can I do a conditional merge queries with left-outer join on [Municipality] if
Municipality[Duplicate] = null
? If this condition is False in a row, then the output column's row's value should be Municipality[Duplicate]. So the Municipal table should become:
| Municipality | Duplicate | Municipality Type |
| ABC | null | Township |
| DEF | Borough | Borough |
| DEF | Township | Township |
| GHI | null | City |
This conditional merge can be accomplished by creating new tables that are derived from the original tables. I think that it can also be accomplished by adding more intermediary (maybe conditional) columns. Which solution would be efficient? Thanks!
2 Replies
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"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.)
