Hi, newbie BI person here...
Short version: Can someone point me to a method to extract each excel file within a specified Sharepoint Folder to it's own query, please?
Long version: I have a Sharepoint folder that excel file database reports, and want to simplify extracting each file within that folder to it's own query to be used in a BI dashboard.
I have an initial query that outputs a list of the files in that location with a Content column showing Binary.
(= Table.SelectRows(Source, each [Folder Path] = "https://ourcompany.sharepoint.com/sites/Reporting/Shared Documents/Database Reports/")).
I right click on that field for each record and select Add As New Query, which shows me the Excel file object.
(= #"Filtered Rows"{[Name="Report1.xlsx",#"Folder Path"="https://ourcompany.sharepoint.com/sites/Reporting/Shared Documents/Database Reports/"]}[Content])
Right clicking on the icon and selecting Excel Workbook gives me a worksheet table, with a Data field listing Table.
= Excel.Workbook(#"Report1 xlsx_https://https://ourcompany.sharepoint.com/sites/Reporting/Shared Documents/Database Reports/")
There'll only be one worksheet, and a click on that Table value opens up the report (and then I make the first row headers).
= #"Imported Excel Workbook"{[Item="Page 1",Kind="Sheet"]}[Data]
So, my first ask is whether there's a good way to abbreviate that process? Secondly, is there a good way of making it as easy as possible for someone to add to the available queries in the dataset - ideally by just adding a report to the Sharepoint folder and it appearing in the set, or by manually running a function on the initial file list in BI?
Currently, our database outputs scheduled reports via email. Power Automate picks up the emails and saves the attachments to Sharepoint. I then create a new query in a dataset and pull the data through for use in dashboards. It's that last step I want to make easier to reduce manual work needed. Once it's done, we should be ok with just refreshing the set when the reports are overwritten.
Any help much appreciated.
ta
Matt
