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, defined ranges etc.

The other function - Excel.Workbook gets all objects, including sheets, that's good.

But this function is usually used in a new workbook to get data from the original workbook.

I need to use this function in currently opened workbook, so actually I refer to the same workbook.

This works OK on the local hard drive, but if I move to OneDrive, I get an error "DataSource.Error: the process cannot access the file because it is being used by another process."

Is it possible to combine all sheets of a workbook with a query in the same workbook, when the workbook is stored in OneDrive?

Thank You very much.

Zdenek Moravec

Cesky Krumlov, Czechia

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

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.

3 Replies