Merge 2 tables with duplicate values with power query

[mdkainth]

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.

Dept	Value1	Value2		Dept	Value3
D1	6000	ABC123		D1	A000
D1	6001	ABC124		D1	A001
D2	6002	ABC125		D2	A002
D3	6003	ABC126		D2	A003
D4	6004	ABC127		D3	A004
D2	6005	ABC128		D3	A005
D4	6006	ABC129		D4	A006
D3	6007	ABC130		D4	A007

 

[AliGW]

Not unless each of those rows has a matchable unique ID.

 

[Bob Phillips]

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.

 

[masterelaichi]

can you post a sample of the desired result you are looking for?

 

[mdkainth]

Here's the sample output

Dept	Value1	Value2	Value3
D1	6000	ABC123	A000
D1	6001	ABC124	A001
D2	6002	ABC125	A002
D3	6003	ABC126	A004
D4	6004	ABC127	A006
D2	6005	ABC128	A003
D4	6006	ABC129	A007
D3	6007	ABC130	A005

 

[mdkainth]

That returns all values from table 2 for each value of table 1. I need only one match.

Dept	Value1	Value2	Value3
D1	6000	ABC123	A000
D1	6001	ABC124	A001
D2	6002	ABC125	A002
D3	6003	ABC126	A004
D4	6004	ABC127	A006
D2	6005	ABC128	A003
D4	6006	ABC129	A007
D3	6007	ABC130	A005

 

[mdkainth]

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

 

[mdkainth]

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?

 

[masterelaichi]

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

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

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

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"

 

[Bill Szysz]

Table1

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

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

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"

 

[mdkainth]

Awesome! Thanks Bill, exactly what I needed.

 

[Bill Szysz]

You're welcome