Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Merge 2 tables with duplicate values with power query

  1. #1
    Seeker mdkainth's Avatar
    Join Date
    Sep 2020
    Posts
    6
    Articles
    0
    Excel Version
    Microsoft 365

    Merge 2 tables with duplicate values with power query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,472
    Articles
    0
    Excel Version
    Office 365 Subscription
    Not unless each of those rows has a matchable unique ID.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,821
    Articles
    0
    Excel Version
    O365
    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.

  4. #4
    Acolyte masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    20
    Articles
    0
    Excel Version
    2016
    can you post a sample of the desired result you are looking for?

  5. #5
    Seeker mdkainth's Avatar
    Join Date
    Sep 2020
    Posts
    6
    Articles
    0
    Excel Version
    Microsoft 365
    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

  6. #6
    Seeker mdkainth's Avatar
    Join Date
    Sep 2020
    Posts
    6
    Articles
    0
    Excel Version
    Microsoft 365
    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

  7. #7
    Seeker mdkainth's Avatar
    Join Date
    Sep 2020
    Posts
    6
    Articles
    0
    Excel Version
    Microsoft 365
    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?

  8. #8
    Seeker mdkainth's Avatar
    Join Date
    Sep 2020
    Posts
    6
    Articles
    0
    Excel Version
    Microsoft 365
    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?

  9. #9
    Acolyte masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    20
    Articles
    0
    Excel Version
    2016
    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"

  10. #10
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    119
    Articles
    0
    Excel Version
    Excel 365
    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"
    Attached Files Attached Files
    Last edited by Bill Szysz; 2020-09-25 at 12:06 AM.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •