none
Merging data from multiple worksheets with diffrent columns RRS feed

  • Question

  • Hey,

    I am quite new to the PowerQuery and I am looking for solution to my problem. I've searched several topics and vieved some videos but I can't find the solution to my problem.

    I have multiple workbooks with sheets(the same name) that contains data. Some of these sheets has columns with headers like A, B, C, D but some of them had additional columns and their structure looks like that: A, B, B1, B2, C, D. My question is how to combine them to get output like that: A, B, B1, B2, C, D. Other worksheets can have other columns. Is it posiible to merge them using PowerQuery?


    Monday, August 6, 2018 6:36 PM

Answers

  • Hi there. If you want the columns that don't exist on a particular sheet to be filled with nulls, then you should be able to Append the sheets.

    Ehren

    Monday, August 6, 2018 11:46 PM
    Owner
  • Hi tomkrk

    Had to do something similar recently. Instead of hard-coding the Folder Path & Sheet name in the query code I created a Table (named TBL_QryParams here) in the workbook that receives the query output

    Query code

    let
        // Parameters from Workbook's table 'TBL_QryParams'
            // NB: with FolderPath in a Table (vs. a Named var.) the query was failing with (no problem with FolderPath stored in a Named var.):
            // "Formula.Firewall: Query 'NameOfTheQuery' (step 'TablesAppended') references other queries or steps, so it may not directly access a data source..."
            // WORKAROUND: Query options > CURRENT WORKBOOK Privacy > Ignore the Privacy Levels and potentially improve performance
        QryParamsTable = Excel.CurrentWorkbook(){[Name="TBL_QryParams"]}[Content],
            FolderPath = QryParamsTable{0}[Value],
            SheetName = QryParamsTable{1}[Value],
    
        // Workbooks in folder
        WbookNames = Folder.Files(FolderPath)[Name],
    
        // Transform Workbooks List as List of Tables
        TablesToAppend = List.Transform(WbookNames, each
            let
                Source = Excel.Workbook(File.Contents(FolderPath & "\" & (_)), null, true),
                Sheet = Source{[Item=SheetName, Kind="Sheet"]}[Data],
                OutputTable = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true])
            in
                OutputTable
            ),
        TablesAppended = Table.Combine(List.Transform(TablesToAppend, each (_))),
        ColumnNamesSorted = List.Sort(Table.ColumnNames(TablesAppended)),
        TableColumnsReordered = Table.ReorderColumns(TablesAppended, ColumnNamesSorted)
    in
        TableColumnsReordered

    Note the important comment I bolded above. Hope this helps

    Thursday, August 9, 2018 3:51 PM

All replies

  • Hi there. If you want the columns that don't exist on a particular sheet to be filled with nulls, then you should be able to Append the sheets.

    Ehren

    Monday, August 6, 2018 11:46 PM
    Owner
  • Hi tomkrk

    Had to do something similar recently. Instead of hard-coding the Folder Path & Sheet name in the query code I created a Table (named TBL_QryParams here) in the workbook that receives the query output

    Query code

    let
        // Parameters from Workbook's table 'TBL_QryParams'
            // NB: with FolderPath in a Table (vs. a Named var.) the query was failing with (no problem with FolderPath stored in a Named var.):
            // "Formula.Firewall: Query 'NameOfTheQuery' (step 'TablesAppended') references other queries or steps, so it may not directly access a data source..."
            // WORKAROUND: Query options > CURRENT WORKBOOK Privacy > Ignore the Privacy Levels and potentially improve performance
        QryParamsTable = Excel.CurrentWorkbook(){[Name="TBL_QryParams"]}[Content],
            FolderPath = QryParamsTable{0}[Value],
            SheetName = QryParamsTable{1}[Value],
    
        // Workbooks in folder
        WbookNames = Folder.Files(FolderPath)[Name],
    
        // Transform Workbooks List as List of Tables
        TablesToAppend = List.Transform(WbookNames, each
            let
                Source = Excel.Workbook(File.Contents(FolderPath & "\" & (_)), null, true),
                Sheet = Source{[Item=SheetName, Kind="Sheet"]}[Data],
                OutputTable = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true])
            in
                OutputTable
            ),
        TablesAppended = Table.Combine(List.Transform(TablesToAppend, each (_))),
        ColumnNamesSorted = List.Sort(Table.ColumnNames(TablesAppended)),
        TableColumnsReordered = Table.ReorderColumns(TablesAppended, ColumnNamesSorted)
    in
        TableColumnsReordered

    Note the important comment I bolded above. Hope this helps

    Thursday, August 9, 2018 3:51 PM