cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBInewbie21
Helper II
Helper II

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             50

2                   67            50

5                  50             50

 

Is it possible without creating multiple columns ?

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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:

image.png

 

 

 

 

 

 

 

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
v-xiaotang
Community Support
Community Support

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
Super User
Super User

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:

image.png

 

 

 

 

 

 

 

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

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:
image.png

The formula

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


Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.