Notifications
Clear all
Power Query
2
Posts
2
Users
0
Reactions
160
Views
Topic starter
I need to build a list of worksheet tabs in the current workbook that keeps updated. I need it to work with the web as well as desktop, but the environment is locked down, so I can't use VBA/macros.
I get it mostly working via OneDrive with this query:
let
Source = Excel.Workbook(Web.Contents("https://domain-my.sharepoint.com/personal/user_domain/Documents/TfNSW/ValueCreationFramework-Calculator_v9.xlsm"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each Text.StartsWith([Name], "Item ")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Name", "ItemsListPQOD"}})
in
#"Renamed Columns"
But, it fails when the filename is changed.
I have also tried via Sharepoint... but not sure how to get the filename variable into the query:
let
Source = SharePoint.Files("https://domain-my.sharepoint.com/personal/user_domain/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "TfNSW") and Text.StartsWith([Name], "ValueCreationFramework-Calculator"))
in
#"Filtered Rows"
The last query only shows the various files, rather than the sheets in my file.
Posted : 17/01/2024 5:24 pm
Hi Mike,
This shows indeed a list of files, but you have to filter the list for xlsx file extension, then add a column with the formula Excel.Workbook([Content]), the content of the file is in the Content binary column:
Posted : 21/01/2024 12:29 am
