Excel Data Model File Path Change

Question

My boss created an Excel data model before I joined the company. He created the data model and queried the data from files within his personal drive. Now that I am here, he wants me to have access to these files and has moved them into a shared folder. The thing is, all of the queries now have the wrong file path. Is the only way to fix this to manually update the file path within the Source step of the power query editor? If there is an easier way, please let me know!

sergeibaklan · Answer

CalebF22&nbsp;
That's to update manually. For future updates use paths as parameters, at least folder part of it.

calebf22 · Answer

Could you elaborate further on the future updates?

sergeibaklan · Answer

CalebF22&nbsp;
If you have queries like
let
    Source = Excel.Workbook(File.Contents("C:\Test	estA.xlsx"), null, true),
    Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet
you may create parameter

and change queries to
let
    Source = Excel.Workbook(File.Contents( pFilePath &amp; "testA.xlsx"), null, true),
    Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet
Next the only you need is to edit parameter at one place. In general you may use parameters for full filenames; or keep them in grid, call by another query and combine in main queries, etc. Depends on your needs.

jtucker · Answer

I'm trying to use this in Power Query for Excel (Not Power BI)I created a Parameter for the windows user name as that is the only portion of the data source file path for sharepoint that changes. I am attempting to insert it into but it seems Excel will not allow my to use a Parameter in this fashion.I get this error below. Any ideas?"Formula.Firewall: Query 'My Query' (step 'Filtered Rows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

sergeibaklan · Answer

In general Parameter is also the query. Actually inserting the parameter you combine two queries and above error could appear depends on how exactly you do that. More details and how to fix is here How to fix the Formula.Firewall error in Power Query (2 ways)
I'd start from Ignore Privacy Level in options, usually it helps.

Forum Discussion

Excel Data Model File Path Change

5 Replies

Resources