Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Passing paramater on to List.Range function

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!

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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's avatar
      Anonymous
      Not applicable

       

      This is what my Power Query privacy settings look like:

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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