# Power Query - data from current Workbook



## zico8 (Jul 23, 2018)

Hi,

I have Query that collect data from sheets in the same workbook.
The first line at this moment looks like:

= Excel.Workbook(File.Contents("C:\Documents\my_excel_file.xlsm"), null, true)


How can I change it that it would still work even if I change file location?


----------



## Ron Coderre (Jul 23, 2018)

Try this:
- Enter the file path in a cell (include the ending back-slash)
- Name that cell FilePath
- Select that cell and....Power_Query.From_Table/Range

That will open the Power Query Editor
- Remove all steps EXCEPT the Source step
- Right-Click on the file path cell...Select: Drill Down
(That will establish the file path as a text value that can be used elsewhere)

In your query
Replace this: 
	
	
	
	
	
	



```
= Excel.Workbook(File.Contents("C:\Documents\my_excel_file.xlsm"), null, true)
```
With this: 
	
	
	
	
	
	



```
= Excel.Workbook(File.Contents([COLOR=#0000ff]FilePath & "my_excel_file.xlsm"[/COLOR]), null, true)
```

Now your query will use the FilePath variable as the file location.

Is that something you can work with?


----------



## zico8 (Jul 23, 2018)

Thanks Ron,

I tried this and get error: "Formula.Firewall: Query 'PQ - Get Data' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."


----------



## VBA Geek (Jul 23, 2018)

zico8 said:


> I tried this and get error: "Formula.Firewall: Query 'PQ - Get Data' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."




Formula Firewall Error


----------



## Ron Coderre (Jul 23, 2018)

Something's incorrect.

These are my 2 queries:
Query Name: FilePath

```
let
    Source = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1
```

Query Name: Query1

```
let
    Source = Excel.Workbook(File.Contents(FilePath & "my_excel_file.xlsm"), null, true)
in
    Source
```

Does that help?


----------



## zico8 (Jul 23, 2018)

It seems like I am doing the same.

I have Query:

```
let
    Source = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1
```

and the result below is:
C:\Documents\


And my main Query begins:

```
let    Source = Excel.Workbook(File.Contents(FilePath & "my_excel_file.xlsm"), null, true),
```
 - then I got that error.


If I manually change for:

```
let    Source = Excel.Workbook(File.Contents("[COLOR=#333333]C:\Documents\" [/COLOR]& "my_excel_file.xlsm"), null, true),
```
 - everything works fine


----------



## macfuller (Jul 23, 2018)

Try changing your privacy settings to None.  Query settings are via the Data ribbon, the last option under Get Data.


----------



## zico8 (Jul 23, 2018)

It looks like changing privacy settings helped.

Thanks everyone for suggestions


----------

