Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.