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

Question

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?

horseyride · Accepted Answer · 2020-03-02 16:50:34Z

1

Try

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

answered Mar 2, 2020 at 16:50

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darren Bartrup-Cook Over a year ago

This is a tough one - two answers at the same time, both work and are very similar. Going for this one as it returns the value, while the one from @Ricardo returns a table (I think - this one has the ABC icon next to it, Ricardos has a small 2-column table icon). Thanks both for the answers :)

Ricardo Diaz · Accepted Answer · 2020-03-02 16:51:41Z

1

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

answered Mar 2, 2020 at 16:51

Ricardo Diaz

5,7262 gold badges24 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related