Match ID that contains in another Table

[nelsonwynn]

I'm a beginner of Power BI. I would like to know how can I match two tables which have IDs are not same. Please kindly see the following example.

Table 1 ->

Table2 ->

The result I want ->

Thank you so much.

[rajendraongole1 · Accepted solution]

Hi @nelsonwynn - you can merge the two tables in power query editor with Name is common column in both use left outer join

expand the table with ID column later add a custom column as below:

if [ID]= null then "Missed" else "Matched"

final output:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1SyExR0lHyS8xNBVKOufmleSVKsTrRSoYGBoZAEZfEMrACUwMDmLARkOudmlOWmQdkmCHEjYHc4JLUslSQuDlC3ATIDUpMzkjNATIsEOKmIPH8YpC9liDRWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tab1 = _t, #"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Tab1", type text}, {"(blank)", type text}, {"(blank).1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order id", Int64.Type}, {"Name", type text}, {"Amount", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Name"}, T2, {"Name"}, "T2", JoinKind.LeftOuter),
#"Expanded T2" = Table.ExpandTableColumn(#"Merged Queries", "T2", {"ID", "Name"}, {"ID", "Name.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded T2", "Custom", each if [ID]= null then "Missed" else "Matched"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Status"}})
in
#"Renamed Columns"

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[nelsonwynn]

Oh sorry, I make mistake from sample data. I would like to join with the ID. the data is like that. I'll repair my sample data. Thank you.

[rajendraongole1]

Hi @nelsonwynn -Great!!

If i did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[nelsonwynn]

Hi @rajendraongole1 ,

Thank you so much for your answer.