Forum Discussion
Excel Data Model File Path Change
That's to update manually. For future updates use paths as parameters, at least folder part of it.
If you have queries like
let Source = Excel.Workbook(File.Contents("C:\Test\testA.xlsx"), null, true), Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data] in Sheetyou may create parameter
and change queries to
let Source = Excel.Workbook(File.Contents( pFilePath & "testA.xlsx"), null, true), Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data] in SheetNext 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.
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."
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.
