Forum Discussion

StefanoT1982's avatar
StefanoT1982
Regular Visitor
1 year ago
Solved

Evaluating a column through steps without hardcoding the steps' names

Assume I have a simple table with 2 steps:

let

    Step1 = #table (type table[Names = Text.Type, Money = Int64.Type], {{"John", 1}, {"Jack", 2},{"Mary", 4} }),

    Step2 = Table.Skip(Step1,1)

   

in

Step2

 

If I want to check the evolution of the total sum of the “money” column I can create a record like this:

 

  CheckManual = [

        Step1 = List.Sum(Table.Column(Step1, "Money")),

        Step2 = List.Sum(Table.Column(Step2, "Money"))

        ]

But this hard codes the previous steps names. Is there a way to work this around?

 

Also something that would required a simple copy paste in the advanced editors of the steps I want to evaluate would be fine.

Eg I tried to transform the steps names into a list like in the example below, but could do a similar thing with the “money” column through the steps…

 

  StepsNames = Record.FieldNames([

        Step1 = #table (type table[Names = Text.Type, Money = Int64.Type], {{"John", 1}, {"Jack", 2},{"Mary", 4} }),

        Step2 = Table.Skip(Step1,1)

        ])

 

Thanks!

Tips and Tricks
  • AlienSx's avatar
    AlienSx
    1 year ago
    let
    steps = 
        [
            Step1 = #table (type table[Names = Text.Type, Money = Int64.Type], {{"John", 1}, {"Jack", 2},{"Mary", 4} }),
            Step2 = Table.Skip(Step1,1)
        ],
    money_evolution = ((fields) => Record.FromList(List.Transform(fields, (x) => List.Sum(Record.Field(steps, x)[Money])), fields))(Record.FieldNames(steps))
   
in
    money_evolution

3 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    1 year ago
    let
    steps = 
        [
            Step1 = #table (type table[Names = Text.Type, Money = Int64.Type], {{"John", 1}, {"Jack", 2},{"Mary", 4} }),
            Step2 = Table.Skip(Step1,1)
        ],
    money_evolution = ((fields) => Record.FromList(List.Transform(fields, (x) => List.Sum(Record.Field(steps, x)[Money])), fields))(Record.FieldNames(steps))
   
in
    money_evolution
    • StefanoT1982's avatar
      StefanoT1982
      Regular Visitor
      1 year ago

      that's great! would you mind explaining how it works? I'm little familiar with the "=>" function syntax

      • AlienSx's avatar
        AlienSx
        Super User
        1 year ago

        "steps" is record. 

        Record.FieldNames(steps)

        reads it's fields' names and gets list of names (of steps). 

        List.Transform

         goes over the list, picks up every other (step) name and calculates List.Sum of Money column values. 

        Record.FromList

        folds the results into another record with steps names and Money totals. 

        M is functional language so get used to functions syntax and usage.