Power Query Dynamic File Path & Name

[DaytoData]

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]

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]

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