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: 

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

 

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

     

6 Replies

  • 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.

  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    3 years ago

    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's avatar
      AntrikshSharma
      Community Champion
      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"  
                    )  
                )
            } 
        }
    )