2 column merge dynamic

[Shan_Drex12]

Hi All,

Quick question: I have a data sheet that only has this column Code alongwith some other data (Sheet 1. Full Data)

Code

AR

BA

BG

BH

BO

BT

CP

CR

DA

But, I just added another data sheet with both code and name. (Sheet 2.FullNames)

Code	Names
AR	International Air
BA	Brokerage Air Freight
BG	Brokerage Ground
BH	Backhaul
BO	Brokerage Ocean
BT	Bulk Tanker
CP	Customer Pickup
CR	Courier
DA	Domestic Air

I want to have it where if the previous sheet has one of these codes above instead of naming as the abbv. use the full name. By dymanically matching the codes from (Sheet 2. FullNames)

[Shan_Drex12]

Hi @smpa01 

I have another sheet that I want to match but this time, the list is longer. I tried using your formula above, but when I hit close and apply it takes a very long time load... over 30 mins. Is there a faster way to run the data by using a different formula to custom merge two data sets?

[smpa01 · Accepted solution]

@Shan_Drex12 

can you please try this as a custom column. I have removed the last column from the lookuptable to test out the code.

//table t2
let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/2-column-merge-dynamic/m-p/1645518#M50293"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(8) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(8) > * > TR > :nth-child(2)"}}, [RowSelector="TABLE:nth-child(8) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code", type text}, {"Names", type text}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",1)
in
    #"Removed Bottom Rows"

//table t1
let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/2-column-merge-dynamic/m-p/1645518#M50293"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(4) > * > TR > :nth-child(1)"}}, [RowSelector="TABLE:nth-child(4) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each let x = Text.From([Code]),
y = List.PositionOf(t2[Code],x),
z = try t2[Names]{y} otherwise null in z)
in
    #"Added Custom1"

[Shan_Drex12]

Thanks @smpa01, Is ther another we can do this as well. I'm trying this out with another file, I have and its giving me all nulls