Forum Discussion
Solved
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...
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 ExpandTablesFile path is in File-Info, remove ?web=1 at the end.
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
ExpandTablesFile path is in File-Info, remove ?web=1 at the end.
Also Web.Contents instead of File.Contents, that's the magic touch.
It works perfect, thank You very much.Zdenek
Zdenek_Moravec , you are welcome, glad to help
