Passing paramater on to List.Range function

[Anonymous]

Hello everybody,

I am currently trying to feed the List.Range function with parameters via a custom function, which sadly does not seem to be working.

The custom functions extract a value from the following spreadsheet table:

The custom function looks like this:

(ParameterName as text) =>
 let
     ParamSource = Excel.CurrentWorkbook(){[Name="tblParameter"]}[Content],
     ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
     Value=
	if Table.IsEmpty(ParamRow)=true
	then null
	else Record.Field(ParamRow{0},"Value")
in
    Value

The custom function returns the correct result:

Yet when I insert the value returned by the custom function into the following formula, things go south:

= Table.SelectColumns(Source,List.Range(Table.ColumnNames(Source),0,14))

= Table.SelectColumns(Source,List.Range(Table.ColumnNames(Source),0,fnGetColumn("Submitted Forms: to Column")))

I am getting this error:

If I were to use a Power Query parameter, though, the List.Range function would accept it. But I need to extract the value from the spreadsheet.

Hope someone can help me out here!

Best regards!

[Anonymous]

Hi @Anonymous,

Could you please check the data source settings in the File-Options-Datasorce settings? I think your datasource for the main query and the workbook has different or none privacy settings. Set them both to same.

Kind regards,
JB

[Anonymous]

This is what my Power Query privacy settings look like:

Where are the other privacy settings I am supposed to compare this with?

[Anonymous]

This is your Excel file - change it to say Organisational. There has to be the same settings for your main datasource - unless sourced from the same file (?). Change it to Organisational too.

Kind regards,
JB

[Anonymous]

Where do I see the settings for the main datasource? Btw, the main source is a Files from Folder query.

[Anonymous]

Hi @Anonymous 

Can this be the reference to a data files folder one line above the Excel workbook? Otherwise, you may be able to find it in the Global list (switch at the top of the Dialog window).

Kind regards,

JB