PowerQuery - Set up Query Parameters to avoid hardcoded paths

[simo12447]

Hi All -

my name is Simone and I am trying to fix this issue.

my query path is

= Excel.Workbook(File.Contents("C:\Users\Simone\Box Sync\4_International\GM Entity 4049 Database.xlsx"), null, true)

and I want to make the Simone path dynamic.. right now I m the only one that can refresh the file. I would like also other people doing it.

How should I approach the problem? I spent the last 3 hours reading and tyring but no success.

Thanks a lot in advance.
Simone

[kersplash]

What do you mean by "right now I m the only one that can refresh the file"?

Do the other users have Power Query activated?

The source file needs to be able to be seen by the users trying to refresh the query.

[AliGW]

Place the file at a location that all users are able to access (maybe a network location) and change the hard-coded path name to reflect this.

[simo12447]

the file is shared on box.. so everyone is opening box should be able to refresh it. so the path is not recognized.

i.e. on his pc Marco should have = Excel.Workbook(File.Contents("C:\Users\Marco\Box Sync\4_International\GM Entity 4049 Database.xlsx"), null, true)

[kersplash]

But that would mean each user needs to maintain a copy of the up-to-date source file on their computer.

Store it in a network location or common drive location and point to that.

[AliGW]

Yes, but not everybody is a user called Marco!

You need the file to be located somewhere that is not in a local file path (e.g. a network drive). The C drive is local to the machine you are working on.

[CK76]

It will depend on your folder structure.

Where would the file containing query will be placed? Could it be anywhere in the local drive? Or is there specific designated location within Box Sync?

If latter, I'd probably do something like...
Lets say the workbook with the query is stored in the same folder as the file being queried.
1. Add Parameter sheet to the workbook with query.
2. Add in any cell of the sheet, following formula. This will give folder path to where the file is saved.

Please Login or Register  to view this content.

3. Select the cell and make it named range with name "vPath".
4. In query editor add following line after "let".

Please Login or Register  to view this content.

5. Replace "Source =" line with...

Please Login or Register  to view this content.

Let me know how it works out.

Edit: If it gives error message about security level. Set both the workbook and queried workbook to Organizational.

[simo12447]

I am storing the file in a box folder (cloud). Then i synchronized the box folder on my pc... and I build my file. I was reading that there is a way to make the name dynamic
read this...



techcommunity.microsoft.com/t5/Excel/Power-Query-Source-from-Relative-Paths/td-p/206150


but I have 0 idea on how to apply on my case.

[simo12447]

i ll try. it sounds what I want to do.

[CK76]

techcommunity.microsoft.com/t5/Excel/Power-Query-Source-from-Relative-Paths/td-p/206150

Yes, that's the same method as my post, and standard method for dynamic folder path (to a point). Queried file must have consistent path relative to workbook with query.

[simo12447]

at the end I used this one youtube.com/watch?v=6qUUxAsSomU . Now I have the issue of

Formula.Firewall: Query 'f_4000000-6999999' (step 'Merged Queries') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.


I want to die

[AliGW]

It's not worth dying for ...

Everything referenced in the file in question needs to be available in that one central universally accessible network place. Otherwise you will get errors like this.

[simo12447]

I agree... but my architecture is to have 1 source and 1 mapping shared for all the file. People are crazy and the modify the mapping..

[CK76]

Formula firewall, is often result of how you build your query dependency.

If the initial query is used within another query, try combining two into single query in "M".

Have a read of link below for more detail on how to avoid formula firewall.
https://blog.crossjoin.co.uk/2017/06...irewall-error/