Update Source By Refresh Instead of Editing Query Detail

[Calebharriott]

Hi There.
I work with a lot of data that it is exported from our database to excel grids or text files and then used to generate various weekly & monthly reports.
To keep the original source data for the different periods (i.e. export for March16 summary as of Week 1 of the month, Week 2 and so on) I save the excels/text files either with different file names for the period in question or inside different folders named for the period in question.
What this means is each week/month I need to adjust the Query Source to update the existing query to the latest source to to re-use the query I've built and update.
Wondering if there is a way to update the source by either connecting to detail in the sheet it self or perhaps triggering directory via another query?
Open to any suggestions.

 

[Brian Smith]

I like to create a simple table with a parameter that I can update from the excel workbook. I pull that parameter into a query and drill down to it as a hard coded value. I then name my files such that substituting that query value into the source of another query which directs it to the file location. Example is attached.

 

[Calebharriott]

Hi Brian. I was thinking something like that.
I'm using Excel 2016 and I got this error when exploring your example? "Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

To then edit the source code to get from a different workbook how would you edit this source string = Excel.Workbook(File.Contents("\\psf\Home\Desktop\10. Mar 16\16 Mar 16.xlsx"), null, true) to reference say Month Query instead of 10. Mar 16 & Day Query for 16 Mar 16

 

[Brian Smith]

Hey Caleb,

I'm attaching an updated file that I think should "parameterize" your file based on the file naming convention you have in your message. I didn't know if the "10." would always stay the same or if it could change so I made it a parameter.

Assuming this isn't sensitive data and you aren't concerned about privacy you can just tell power query to ignore the privacy settings and that error will go away. If it is sensitive and privacy is a concern, I believe Ken Puls has written some posts about how to fix this the right way.

 

[Calebharriott]

Hi Brian
Okay thats perfect! Thanks so much for your assistance.
To explain the 10. it was referencing the Financial Year Month Number though it should have actually been a 9 since it was the month of March but I'm in the middle of all my April reports so had the 10th month on the brain .

 

[Brian Smith]

ah - didn't think of a fiscal year. You could automate that then if you were so inclined.