Merge 2 tables with duplicate values with power query

mdkainth

New member
Joined
Sep 11, 2020
Messages
6
Reaction score
0
Points
0
Excel Version(s)
Microsoft 365
Hi,

I am trying to merge 2 tables using power query. Each table has a column that has repeating or duplicate values. I want to merge the 2 tables based upon this field. e.g. D1 in table 1 to merge with first D1 Value 3 in table 2. The second D1 in table to merge with second D1 in table 2 and so on. Is it possible? Please help.

DeptValue1Value2DeptValue3
D16000ABC123D1A000
D16001ABC124D1A001
D26002ABC125D2A002
D36003ABC126D2A003
D46004ABC127D3A004
D26005ABC128D3A005
D46006ABC129D4A006
D36007ABC130D4A007
 
Add each table as a separate query, then do a merge queries from Table1 to Table 2, doing a full outer join. Then expand the table created by the merge.
 
can you post a sample of the desired result you are looking for?
 
Here's the sample output

Dept
Value1Value2Value3
D16000ABC123A000
D16001ABC124A001
D26002ABC125A002
D36003ABC126A004
D46004ABC127A006
D26005ABC128A003
D46006ABC129A007
D36007ABC130A005
 
That returns all values from table 2 for each value of table 1. I need only one match.

Dept
Value1Value2Value3
D1
6000ABC123A000
D1
6001ABC124A001
D2
6002ABC125A002
D3
6003ABC126A004
D46004
ABC127A006
D26005
ABC128A003
D46006ABC129A007
D36007ABC130A005
 
That's an interesting thought. Giving each key identifier a number. e.g. D11, D12. That can give the desired result. However I do not know if even that is possible? :rolleyes2
 
Just came across this formula to add an incremental number
=IF(A2=A1,C1+1,1)

Is there a way we can use similar formula in power query?
 
Not sure if this is the most efficient way of doing it. I added an index column to each input table and did a merge on it

Table1

Code:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc67DYAwDATQVSLXKfyLAyWELaLsvwYgGQu5cvF0d54TLipQwRDxvcc5iKXAqj+hEHVhFw5pLuIiIeaiLhrSU1sL2VLGQva00z+R5/+1bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t, Value1 = _t, Value2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", type text}, {"Value1", Int64.Type}, {"Value2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Table2
Code:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjFUUNJRcjQwMFCK1UHiGkK4RlCuESrXGMI1hnJNULmmEK4JlGuGyjVXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t, Value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", type text}, {"Value3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Output
Code:
let
    Source = Table.NestedJoin(Table1, {"Index"}, Table2, {"Index"}, "Table2", JoinKind.RightOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "Table2", {"Value3"}, {"Value3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Index"})
in
    #"Removed Columns"
 
Table1
Code:
let    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc67DYAwDATQVSLXKfyLAyWELaLsvwYgGQu5cvF0d54TLipQwRDxvcc5iKXAqj+hEHVhFw5pLuIiIeaiLhrSU1sL2VLGQva00z+R5/+1bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t, Value1 = _t, Value2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", type text}, {"Value1", Int64.Type}, {"Value2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Dept"}, {{"tbl", each Table.AddIndexColumn(_, "Idx",1,1), type table}}),
    Custom1 = Table.Combine(#"Grouped Rows"[tbl])
in
    Custom1

Table2
Code:
let    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjFUUNJRcjQwMFCK1UHiGkK4RlCuESrXGMI1hnJNULmmEK4JlGuGyjVXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t, Value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", type text}, {"Value3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Dept"}, {{"tbl", each Table.AddIndexColumn(_, "Idx",1,1), type table}}),
    Custom1 = Table.Combine(#"Grouped Rows"[tbl])
in
    Custom1

Result
Code:
let    Source = Table.NestedJoin(Table1, {"Dept", "Idx"}, Table2, {"Dept", "Idx"}, "Table2", JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "Table2", {"Value3"}, {"Value3"}),
    #"Sorted Rows" = Table.Sort(#"Expanded {0}",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Idx"})
in
    #"Removed Columns"
 

Attachments

  • mdkainth_KenForum.xlsx
    18 KB · Views: 21
Last edited:
Awesome! Thanks Bill, exactly what I needed.
 
Back
Top