Update PowerQuery function to return data from named table column and row, not just row in specific column

0
votes

I have some M code that returns a value from a specified row on a named table in Excel (called fParameters): 

let Parameter=(TableName,RowNumber) =>
    let
        Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
        value = Source{RowNumber-1}[Value]
    in
        value
in Parameter

I use the function to set where I want to load the raw data from: 

Source = Excel.Workbook(File.Contents(fParameters("Parameters",1) & fParameters("Parameters",4)),null, true),
S1_Sheet = Source{[Item="S1",Kind="Sheet"]}[Data],

The code above will look at rows 1 and 4 of the Parameters table to return the source file. It will then get the data from the S1 worksheet on that source file.

The Problem:
I would like to change the function so that I can change the name of the column it looks at. At the moment it looks at the [Value] column. I have tried adding ColumnName as the third parameter for the function, but can't get it to look at that column - at best it looks for a column called ColumnName. 

let Parameter=(TableName,RowNumber,ColumnName) =>
    let
        Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
        value = Source{RowNumber-1}[ColumnName]
    in
        value
in Parameter

I've tried variations with the curly brackets, but can't get it to work. Could someone point me in the right direction please?

2
excelpowerquerym

2 Answers

1
votes

Try 

(TableName,RowNumber,ColumnName) =>
let
Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
value = Table.Column(Source,ColumnName){RowNumber-1}
in value
1
votes

One way to do it is just select the column and then refer to the row

let Parameter=(TableName,RowNumber,ColumnName) =>
    let
        Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
        SelectColumn = Table.SelectColumns(Source, ColumnName),
        SelectValue = SelectColumn{RowNumber-1}
    in
        SelectValue
in Parameter