Power Query - data from current Workbook

[zico8]

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]

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]

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]

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]

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]

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]

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

 

[zico8]

It looks like changing privacy settings helped.

Thanks everyone for suggestions