Forum Discussion
2 column merge dynamic
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)
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"
3 Replies
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"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
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?
