M help

[PBInewbie21]

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             50

2                   67            50

5                  50             50

Is it possible without creating multiple columns ?

[v-xiaotang]

Hi @PBInewbie21 

Have you solved this question with TomMartens's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.❤️

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens · Accepted solution]

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]

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]

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