Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
maclura
Resolver I
Resolver I

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, before to combine them, I have to perform two transformations on each of them, which are:
 
#"Promoted Headers" = Table.PromoteHeaders(SpreadsheetList{0}, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Col1", "Col2", "Col3"}, "Attribute", "Value")
 
unfortunately I don't know how to perform these 2 transformations on each item of the list before to combine the result in a single table.
 
Thank you for any help.
m
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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"
           )
    )
)

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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"
           )
    )
)

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors