Power Query How to combine all sheets of currently opened OneDrive Workbook

Dear colleagues, I want to combine all sheets in my currently opened workbook. Usually, I use = Excel.CurrentWorkbook() as the Source, but this function does not return sheets, only Excel tables, de...

PQ Combine all sheets of the currently opened workbook local.xlsx18 KB

excel

SergeiBaklan

Nov 28, 2023

Zdenek_Moravec

This one works in my case

let
    pPath = "https://tenant.sharepoint.com/personal/"
        & "account/Documents/"
        & "PQ%20Combine%20all%20sheets%20of%20the%20currently%20opened%20workbook%20local.xlsx",
    Source = Excel.Workbook(Web.Contents(pPath), null, true),
    FilterSheets = Table.SelectRows(Source, each ([Kind] = "Sheet") and ([Name] <> "Result")),
    GetTables = Table.SelectColumns(FilterSheets,{"Data"}),
    ExpandTables = Table.ExpandTableColumn(
        GetTables,
        "Data",
        {"Column1", "Column2"}, {"Column1", "Column2"})
in
    ExpandTables

File path is in File-Info, remove ?web=1 at the end.

SergeiBaklan

Diamond Contributor

Nov 28, 2023

Zdenek_Moravec

This one works in my case

let
    pPath = "https://tenant.sharepoint.com/personal/"
        & "account/Documents/"
        & "PQ%20Combine%20all%20sheets%20of%20the%20currently%20opened%20workbook%20local.xlsx",
    Source = Excel.Workbook(Web.Contents(pPath), null, true),
    FilterSheets = Table.SelectRows(Source, each ([Kind] = "Sheet") and ([Name] <> "Result")),
    GetTables = Table.SelectColumns(FilterSheets,{"Data"}),
    ExpandTables = Table.ExpandTableColumn(
        GetTables,
        "Data",
        {"Column1", "Column2"}, {"Column1", "Column2"})
in
    ExpandTables

File path is in File-Info, remove ?web=1 at the end.