Forum Discussion

Helper II

4 years ago

Solved

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

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

TomMartens

Super User

4 years ago

Hey PBInewbie21 ,

what you are asking for requires some understanding of the generic (not to say intricate) workings of data transformation inside Power Query. For a deeper understanding I recommend the articles that you find here:
Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo

Here, it's good to know that basically data transformations are performed for each row. This means that the input for a subsequent step is the previous table, each row of previous table will be streamed into the data transformation task of the step.

Now your requirement is to create a column that "repeats" the MAX value of a certain column, the problem is to access the complete column of the previous table to find the MAX value. Referencing a complete column requires using the table name (the name of the previous step) instead of the value of the current row.
The following screenshot shows what I'm talking about:

The formula

List.Max(#"Changed Type"[Column2])

Hopefully, this provides what you are looking for.

Regards,

Tom

PBInewbie21

Helper II

4 years ago

Thanks for your reply TomMartens but if I do Max of column2 then I brings answer 67 in column3 but I need 50 because max value on column1 is 5