Forum Discussion

maclura's avatar
maclura
Icon for Resolver I rankResolver I
4 years ago
Solved

Looping throughout a list of tables

Hi, I have a list of tables with an unknown number of items: "SpreadsheetList" I have to consolidate all the tables in a sigle table e.g. with  BigTable = Table.Combine(SpreadsheetList) but, bef...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You could create a function out of the applied steps but, in this case, it might be simpler to just smash everything together in one step like this:

    BigTable =
Table.Combine(
    List.Transform(
        SpreadsheetList, each
           Table.UnpivotOtherColumns(
                Table.PromoteHeaders(_, [PromoteAllScalars=True]),
                {"Col1", "Col2", "Col3"},
                "Attribute", "Value"
           )
    )
)

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

You could create a function out of the applied steps but, in this case, it might be simpler to just smash everything together in one step like this:

BigTable =
Table.Combine(
    List.Transform(
        SpreadsheetList, each
           Table.UnpivotOtherColumns(
                Table.PromoteHeaders(_, [PromoteAllScalars=True]),
                {"Col1", "Col2", "Col3"},
                "Attribute", "Value"
           )
    )
)

 

maclura's avatar
maclura
Icon for Resolver I rankResolver I
4 years ago

Thank you so much AlexisOlson, your SOLUTION is just perfect!!!

At the end, I implemented it with a function (so you can check if it's OK ;-))

Here is the full code which exctracts some specific Excel files from a Sharepoint url,

perform some transformations on each spreadsheet of each workbook,

and put the result in a single table.

let
    Source = SharePoint.Files("my Sharepoint url", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "my text filter")),

    WorkbookList = List.Transform(#"Filtered Rows"[Content], each Excel.Workbook(_)[Data]),
    SpreadsheetList = List.Combine(WorkbookList),

        TransformTable = (SpreadsheetList as list) as list => 
    let
        TransformedTable = List.Transform(
            SpreadsheetList, each 
                Table.UnpivotOtherColumns(
                    Table.PromoteHeaders(_, [PromoteAllScalars=true]),
                    {"Col1", "Col2", "Col3"},
                    "Attribute", "Value"))
    in
    TransformedTable,

    SpreadsheetTable = Table.Combine(TransformTable(SpreadsheetList))

in
    SpreadsheetTable

Thank you again for your solution

m