Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago
Solved

Use a formula for a column name

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:  YearOfRe...
Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    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"  
                    )  
                )
            } 
        }
    )

     

AntrikshSharma's avatar
AntrikshSharma
Community Champion
3 years ago

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

  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    3 years ago

    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's avatar
      AntrikshSharma
      Community Champion
      3 years ago

      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.