Power Query can you get around the hardcoded file location for sample file?

[MushroomFace]

My documents are used by multiple team members and they are saved down in different folders by account managed, due to this I use a named range in excel where they put the folder location in to load the raw data. Rather than going into power query and changing the location.

However we have recently changed drives and the sample file location is hardcoded into PQ. The old drive is no longer available so it errors.
I can explain how to fix this but they won’t remember if it happens again and if I wasn’t there they wouldn’t know how to fix it.

I have a FAQs for PQ errors but thinking long term and aiming to keep the document user friendly so they do not need to go into the code to edit, as it just ‘scares’ some people. Is there an alternative way to set up the sample file?

My documents are used by multiple team members and they are saved down in different folders by account managed, due to this I use a named range in excel where they put the folder location in to load the raw data. Rather than going into power query and changing the location.

However we have recently changed drives and the sample file location is hardcoded into PQ. The old drive is no longer available so it errors.
I can explain how to fix this but they won’t remember if it happens again and if I wasn’t there they wouldn’t know how to fix it.

I have a FAQs for PQ errors but thinking long term and aiming to keep the document user friendly so they do not need to go into the code to edit, as it just ‘scares’ some people. Is there an alternative way to set up the sample file?

I’ve been thinking, sample file looking at the named range also - I can’t seem to apply this to the sample file. However I guess the safety feature is if someone links to an incorrect folder with different data it wouldn’t match the sample file so it would error to remind you to check the location given, which is why the sample file is hard coded?


Just thinking out loud and rambling but any advice on the above or alternative options are welcome. Thanks!

[alansidman]

If I understand correctly your issue, then this link may solve your problem.

https://medium.com/@wkrzywiec/passin...y-19ec60797d94

[MushroomFace]

I do use a named range to pull through the file location.

I shouldn't write posts late a night, i was rambling. My issue was with the Sample File, i have figured instead of using the edit and combine to just add a custom column with =Excel.Workbook([Content]), then expand from there and it does not generate a sample file.

Thank you for your input, much appreciated!