Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rootsmusic
Helper I
Helper I

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:

MunicipalityDuplicate
ABCnull
DEFBorough
DEFTownship
GHInull

The Type lookup table has the following columns.  Note that Type[Municipality] has values like Municipal[Municipality].

MunicipalityMunicipality Type
ABCTownship
DEFBorough
DEFTownship
GHICity

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:

MunicipalityDuplicateMunicipality Type
ABCnullTownship
DEFBoroughBorough
DEFTownshipTownship
GHInullCity

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 2
AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1653513032106.png

 

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

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.