Get Data Source for Report

[Anonymous]

I am using Power Query in Excel extract, transform, and load data to create Excel based reports. I am using parameters to dynamically update the source data files, which are in Excel and csv files. Then I have macros to only udpate one report at a time, as new data becomes available. All of the reports have one common data source, and then another data source that varies. 

I have report headers that I want to put the data sources. The report headers are currently linked to the parameter tables. If someone doesn't update that report, then the common data source isn't correct for that report. I would like to pull the Data Sources directly onto the reports so they are always accurate and reflects the data source when the report was last refreshed. 

Does anyone know how to dynamically pull the filepaths of the data sources currenlty used in a query? 

[artemus]

Just use Folder.Contents("Your report folder") or Folder.Files("Your report folder") to get a list of files in the location where they are uploaded too. You can then apply filtering such as choosing the latest file in the folder. Get it down to one row, then drill into that file and finally convert it to csv.

[Anonymous]

Thanks @artemus for the reply. 

I don't think I was very clear in my first message. I set the report for my colleagues to track sales by consumers in given months. The particular population of consumers that they are looking for has to be downloaded externally from a website. So, they get the lists and save to our shared drive. There are 12 datasets (one for each month of the year) that they look at, and that is joined to one source for sales. The report is set up (through list boxes and VBA) to update one report at a time, based on their selections. They get select any sales report from the given folder and any population, as a requirement of their request. 

What I have right now are "report headers" in Excel that are linked to their selections (the parameters for the Power Query queries). The problem is, if they change the parameters and forget to refresh the query, the headers are not accurate as they will reflect the current parameter, but not what the query data source is. Do you know any way to pull the active data soruces? What I am looking for is the information that appears when you hover over a query in the Queries & Connections pane, as shown in the attached image.

[artemus]

You can use #sections[Section1] to get a record of all loaded queries. Just be careful not to do anything with the query that is using that or you will cause a recursion loop.

[Anonymous · Accepted solution]

For every report period, I wrote queries that merged my parameter tables and filtered the results based on the reporting period. Then, I loaded all of those queries to a tab in Excel linked my "report headers" to those tables, and hid the tab. The conditional VBA statement to refresh my queries was updated to refresh the associated query that gets the source data names.

[Anonymous]

Also, I apologize to everyone for using this Board in Power BI, but I haven't found a community for Power Query. My current organization will not allow me to download Power BI on my machine.