Use a formula for a column name

[ValeriaBreve]

Hello,

I am trying to use a formula to call a column name to avoid tedious and long code rewriting (many years to compile....).

In particular, I am reading the Year from the column name: 

YearOfRef = Text.Range(Table.ColumnNames(#"Promoted Headers"){48},4,4)

Now, I have a column that says "Jan 2016 Volume" as an example.

I can refer to it as "Jan " & YearOfRef & " Volume"

But when it comes to other parts of my code, I have an expression like this:

....

each List.Sum([("Jan 2016 Volume")])

....

So in square brackets. How do I insert my YearOfRef there? I cannot seem to find a way.

Thanks!

Kind regards

Valeria

[ValeriaBreve]

@AntrikshSharma Hello, sure! It is used in

#"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Amount", each List.Sum([Jan 2016 Volume])}})

So if I replace the [Jan 2016 Volume] with Table.Column( Table, "Jan 2016 Volume" ) I get the summed volume for the whole column, regardeless of the grouping...

[AntrikshSharma · Accepted solution]

@ValeriaBreve You can use this - 

GroupedRows = 
    Table.Group ( 
        #"Changed Type", 
        {"Product"}, 
        {
            {
                "Amount", 
                (Rows)=> // All Rows & Columns visible for the current Product
                List.Sum (  
                    Table.Column ( 
                        Rows, 
                        "Jan" & Number.ToText ( Year ) & "Volume"  
                    )  
                )
            } 
        }
    )

[ValeriaBreve]

It works very well! Thank you so much 🙂

[AntrikshSharma]

You can use Table.Column ( Table, "Jan 2016 Volume" ) it will return a List and then you can pair it with List.Sum

[ValeriaBreve]

Hello thanks, maybe I did not understand the solution, my whole point is to avoid any hard coded reference to the year, being able to call the column with the help of YearOfRef... otherwise the query will fail for 2017, 2018,... and I have MANY column per year. Thanks!

[AntrikshSharma]

You can replace the 2016 in the code I provided with a refrence to whatever is there in your data, from this point onward I need raw data and final result, it is hard to deduce from text chats what you want.