conditional merge queries with duplicates in column

[Anonymous]

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!

[AlexisOlson]

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]

@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.)