Merge Tables in a List of Tables having different columns - Power Query

Question

I have a List of Tables to Combine together. Each Table contains a main set of columns that are same and another set of columns that are different in each table. I want to combine them so that all the row values fall in the same rows and not on subsequent rows.

List of Tables:

List

Table
Table

Table1:

| A | B | C | R1 | R2 | R3 | R4 |

| 1 | asd | 0.6 | 0.23 | 0.51 | 0.33 | 0.56 |
| 2 | asd | 0.6 | ACF | BCA | FGI | LMN |
| 3 | asd | 0.6 | 1 | 1 | NULL | 1 |
| 1 | ghi | 0.1 | 0.11 | 0.21 | 0.43 | 0.96 |
| 2 | ghi | 0.1 | ACF | DHJ | TRO | PQR |
| 3 | ghi | 0.1 | NULL | 1 | NULL | 1 |
| 1 | hkl | 0.2 | 0.23 | 0.51 | 0.33 | 0.56 |
| 2 | hkl | 0.2 | ACF | BCA | GFI | WQE |
| 3 | hkl | 0.2 | 1 | 1 | NULL | 1 |

Table2:

| A | B | C | R5 | R6 | R7 | R8 |

| 1 | asd | 0.6 | 0.11 | 0.89 | 0.83 | 0.76 |
| 2 | asd | 0.6 | AEE | WCA | FWQ | LON |
| 3 | asd | 0.6 | NULL | 1 | NULL | 1 |
| 1 | ghi | 0.1 | 0.22 | 0.61 | 0.78 | 0.66 |
| 2 | ghi | 0.1 | KJF | ABC | ASM | MOS |
| 3 | ghi | 0.1 | NULL | NULL | NULL | 1 |
| 1 | hkl | 0.2 | 0.44 | 0.51 | 0.45 | 0.77 |
| 2 | hkl | 0.2 | DER | MKE | MKJ | JKL |
| 3 | hkl | 0.2 | 1 | NULL | NULL | 1 |

Final Table:

| A | B | C | R1 | R2 | R3 | R4 | R5 | R6 | R7 | R8 |

| 1 | asd | 0.6 | 0.23 | 0.51 | 0.33 | 0.56 | 0.11 | 0.89 | 0.83 | 0.76 |
| 2 | asd | 0.6 | ACF | BCA | FGI | LMN | AEE | WCA | FWQ | LON |
| 3 | asd | 0.6 | 1 | 1 | NULL | 1 | NULL | 1 | NULL | 1 |
| 1 | ghi | 0.1 | 0.11 | 0.21 | 0.43 | 0.96 | 0.22 | 0.61 | 0.78 | 0.66 |
| 2 | ghi | 0.1 | ACF | DHJ | TRO | PQR | KJF | ABC | ASM | MOS |
| 3 | ghi | 0.1 | NULL | 1 | NULL | 1 | NULL | NULL | NULL | 1 |
| 1 | hkl | 0.2 | 0.23 | 0.51 | 0.33 | 0.56 | 0.44 | 0.51 | 0.45 | 0.77 |
| 2 | hkl | 0.2 | ACF | BCA | GFI | WQE | DER | MKE | MKJ | JKL |
| 3 | hkl | 0.2 | 1 | 1 | NULL | 1 | 1 | NULL | NULL | 1 |

Is this possible to do using M-Language when the List of Tables is expanded?

Answer 1

Hi @Sifar

Assuming a folder with 4 Csv files:

NB: my Delimiter is ; so you might have to change it in step CsvAsTable:

let  
    Source = Folder.Files("PathToCsvFolder"),  
    RemovedOtherColumns = Table.SelectColumns(Source,{"Content"}),  
    CsvAsTable = Table.AddColumn(RemovedOtherColumns, "CsvAsTable", each  
        Table.PromoteHeaders(  
            Csv.Document([Content],[Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.None]),  
            [PromoteAllScalars=true]  
        ),  
        type table  
    ),  
    RemovedContent = Table.RemoveColumns(CsvAsTable,{"Content"}),  
    MergedTables = List.Accumulate(  
        List.Skip(RemovedContent[CsvAsTable]), Table.AddIndexColumn(List.First(RemovedContent[CsvAsTable]),"Index",0,1),  
        (state,current) =>  
            let  
                Merged = Table.NestedJoin(  
                    state,"Index",  
                    Table.AddIndexColumn(current,"Index",0,1),"Index",  
                    "MergedCurrent"  
                )    
            in  
                Table.ExpandTableColumn(Merged, "MergedCurrent",  
                    List.Difference(Table.ColumnNames(current), Table.ColumnNames(state))  
                )      
      
    ),  
    RemovedIndex = Table.RemoveColumns(MergedTables,"Index")  
in  
    RemovedIndex  

Answer 2

Hi @Sifar

Could you double check the following does what you want:

let  
    // Tempo. add an Index column to each table  
    // then merge then on [Index]  
    Source = Table.NestedJoin(  
        Table.AddIndexColumn(Table1,"Index",0,1),"Index",  
        Table.AddIndexColumn(Table2,"Index",0,1),"Index",  
        "MergedTable2"  
    ),  
    // Use List.Difference to identify columns that exist in Table2 but not in Table1  
    ColumnsToExpand = List.Difference(Table.ColumnNames(Table2), Table.ColumnNames(Table1)),  
    // Expand those columns then remove the [Index] that still exist (from Table1)  
    Expanded = Table.RemoveColumns(  
        Table.ExpandTableColumn(Source, "MergedTable2", ColumnsToExpand),  
        {"Index"}  
    )  
in  
    Expanded  

Next time, could you upload & share a file with your data (takes time to re-create)? Thanks in advance

Answer 3

assuming your two tables may have different number of rows, I would use a JoinKind.FullOuter join:

let

table1 = Excel.CurrentWorkbook(){[Name="table1"]}[Content],
table2 = Excel.CurrentWorkbook(){[Name="table2"]}[Content],

common_header_lst = List.Intersect( { Table.ColumnNames(table1), Table.ColumnNames(table2) } ),
common_header_renamed_lst = List.Transform( common_header_lst, each "t2_" & _ ),

merge = Table.Join(
    table1, 
    common_header_lst, 
    Table.RenameColumns(table2, List.Zip({common_header_lst,common_header_renamed_lst}) ), 
    common_header_renamed_lst, 
    JoinKind.FullOuter
),
remove_col = Table.RemoveColumns(merge, common_header_renamed_lst)

in remove_col

Answer 4

What is the source of your data ?