October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
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 ?
Solved! Go to 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
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.
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
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
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
User | Count |
---|---|
105 | |
99 | |
98 | |
86 | |
49 |
User | Count |
---|---|
162 | |
142 | |
132 | |
102 | |
63 |