Passing a filename to fnGetParameter == Error

Nick Burns · May 24, 2017

Hi! :wave:

New to PQ and these forums. I have an error trying to implement Ken's Tip for using a Parameter Table (fnGetParameter)

Here's my original M code:

Code:

let
    Source = Excel.Workbook(File.Contents("D:\Macros\Payroll\union reports\OA\2016-2017\Import\UnionData.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    :
    :
    #"Reordered Columns7" = Table.ReorderColumns....
in
    #"Reordered Columns7"

Here's my new code:

Code:

let
    SolutionFile = fnGetParameter("File"),
    Source = Excel.Workbook(File.Contents(SolutionFile), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    :
    :
    #"Reordered Columns7" = Table.ReorderColumns....
in
    #"Reordered Columns7"

However, I'm getting the following error:
Formula.Firewall: Query 'Data' (step 'Reordered Columns7') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Comfy · May 25, 2017

Give this a go.

Put the source in it's own Query and convert it to a function.

fnGetSource

Code:

()=>
let
Source = Excel.Workbook(File.Contents([COLOR=#333333]fnGetParameter("File")[/COLOR]), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data]
in
Data_Sheet

Your code will then be:

Code:

let
    Data_Sheet = fnGetSource(),
    :
    :
    #"Reordered Columns7" = Table.ReorderColumns....
in
    #"Reordered Columns7"

Nick Burns · May 25, 2017

Nifty -
Since posting the question the error has somehow cleared itself. I've went ahead and implemented your solution just the same.

Thanks!

Passing a filename to fnGetParameter == Error

Nick Burns

Member

Comfy

New member

Nick Burns

Member