# Power Query Dynamic File Path & Name



## DaytoData (Aug 22, 2017)

I have a dynamic table called "NewFilePath" with a header "FILE_LOCATION" which I have imported into PowerQuery using the following M which works:

```
let
    Source = Excel.CurrentWorkbook(){[Name="NewFilePath"]}[Content],
    FileCriteria = Record.Field(Source{0},"FILE_LOCATION")
in
    FileCriteria
```

What I want to do is use the FILE_LOCATION value in instead of C:\Users\cmw1s\Documents\TEST_DATA_SOURCE.xlsx


```
let
    Source = Excel.Workbook(File.Contents([B]"C:\Users\cmw1s\Documents\TEST_DATA_SOURCE.xlsx"[/B]), null, true),
    TEST_DATA_Table = Source{[Item="TEST_DATA",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TEST_DATA_Table,{{"DATE", type date}, {"DATA_VALUE", Int64.Type}})
in
    #"Changed Type"
```

I've tried the following, but received the error "Formula.Firewall: Query 'TEST_DATA' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."


```
let
    NFP = NewFilePath,
    Source = Excel.Workbook(File.Contents(NFP), null, true),
    TEST_DATA_Table = Source{[Item="TEST_DATA",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TEST_DATA_Table,{{"DATE", type date}, {"DATA_VALUE", Int64.Type}})
in
    #"Changed Type"
```

Any help would be greatly appreciated!


----------



## MarcelBeug (Aug 22, 2017)

The easiest way is to adjust the privacy options:
Data tab - Get Data - Query Options - Privacy (under Current Workbook) - check "Ignore the Privacy Levels...._(etc)_"


----------



## mikeymay (Sep 21, 2018)

Hi Marcel

I have had this problem as well but I'm concerned about the caveat when selecting this setting about it possible exposing sensitive data to unauthorized people.

Can you put my mind to rest on this?


Thanks
Michael


----------

