Relative file path for large external data flat files #Parameter

[andydtaylor]

Hi,

I need to reference several multi_GB external text files. However I want my work to be easily ported to another user's computer. So I thought I'd use a parametised query to read current path from a worksheet table generated using a vba macro.

However I seem to be hitting a restriction of some kind because I get the message:

"Formula.Firewall: Query 'SupplierInvoices_20160101_20161231' (step 'Promoted Headers') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination"

What is the right way to do this?


1. Generate parameter

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Directory", type text}}),
    Current_Directory = #"Changed Type"{0}[Current Directory]
in
    Current_Directory

2. Use parameter

let

    Current_Directory = Get_Current_Directory,

    Source = Csv.Document(Web.Contents(Current_Directory & "\" & "SupplierInvoices_20160101_20161231.csv"),[Delimiter="|", Columns=23, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Thanks,


Andy

 

[andydtaylor]

Fixed my own problem LOL

Basically I needed to use "Text.Combine" to concatenate the parameter and string describing the source file

let

    Current_Directory = Get_Current_Directory,
    Current_Directory_Combined = Text.Combine({Current_Directory,"\SupplierInvoices_20160101_20161231.csv"}, ""), 

    Source = Csv.Document(Web.Contents(Current_Directory_Combined),[Delimiter="|", Columns=23, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

[andydtaylor]

BTW it was also necessary to change Query options>Privacy within query editor to "Combine data according to each file's Privacy Level settings"

 

[tallan]

Nice!