SOLVED

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

Brass Contributor

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

3 Replies
best response confirmed by Zdenek_Moravec (Brass Contributor)
Solution

@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

Also Web.Contents instead of File.Contents, that's the magic touch.
It works perfect, thank You very much.

Zdenek

1 best response

Accepted Solutions
best response confirmed by Zdenek_Moravec (Brass Contributor)
Solution

@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.

View solution in original post