cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kbecker0010 Frequent Visitor
Frequent Visitor

Get Data Source for Report

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
kbecker0010 Frequent Visitor
Frequent Visitor

Re: Get Data Source for Report

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.

 

View solution in original post

5 REPLIES 5
Microsoft artemus
Microsoft

Re: Get Data Source for Report

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.

kbecker0010 Frequent Visitor
Frequent Visitor

Re: Get Data Source for Report

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.Data Sources.png

kbecker0010 Frequent Visitor
Frequent Visitor

Re: Get Data Source for Report

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. Smiley Sad

Microsoft artemus
Microsoft

Re: Get Data Source for Report

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.

kbecker0010 Frequent Visitor
Frequent Visitor

Re: Get Data Source for Report

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.

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors