M help

Hi, I need to show values of column2 in new column (column3) based on finding max value from column1 using M code. Here is an example: Column1 Column2 Column3 1 23 ...

Need Help

Show and Tell

Tips and Tricks

Tutorial Requests

TomMartens

4 years ago

Hey PBInewbie21 ,

I'm sorry I have overlooked the business rule.

Of course, this makes the formula a little more complex. The following screenshot shows the expected result:

The essential part of the query creating the custom column "Column3":

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom3", 
    
        each  
            Table.SelectColumns(
                Table.SelectRows(#"Changed Type"
                    , each [Column1] = List.Max( #"Changed Type"[Column1] )
                )
                , {"Column2"}
            ){0}[Column2] //get the value from Column2 in the first row     

        // explicit type of custom column 3
        , Int64.Type
    )
in
    #"Added Custom"

Hopefully, this now provides what you need to tackle your challenge.

Regards,

Tom

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom3", each Table.SelectColumns( Table.SelectRows(#"Changed Type" , each [Column1] = List.Max( #"Changed Type"[Column1] ) ) , {"Column2"} ){0}[Column2] //get the value from Column2 in the first row // explicit type of custom column 3 , Int64.Type ) in #"Added Custom"

Forum Discussion

M help