Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to create a new column "Flag" which will flag the highest percentage of the same Case ID. I am an M amateur and would really appreciate some help.
The formula that I used in excel was this:
=IF(C2=MAXIFS($C$2:$C$7,$A$2:$A$7,A2),1,0)
Solved! Go to Solution.
Hi, @arnabmit ;
You could group by the Case ID column then add a condition column.
1. group by Case ID column.
2.expand the table.
3. add a condition column.
4.delete the Max column.and The final output is shown below:
In additonal , M language like below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgRiQwNVpVgdmIgTEFtCRUyMDKFqzFBEnIHYBCpiZA7TZYwi4gLE5lARY0szqC5zU2QRkBojkEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, Name = _t, Percent = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Case ID", Int64.Type}, {"Name", type text}, {"Percent", Percentage.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Case ID"}, {{"Max", each List.Max([Percent]), type nullable number}, {"a", each _, type table [Case ID=nullable number, Name=nullable text, Percent=nullable number]}}),
#"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"Name", "Percent"}, {"a.Name", "a.Percent"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded a", "Flag", each if [a.Percent] = [Max] then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Max"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"a.Percent", "Percent"}, {"a.Name", "Name"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Percent", Percentage.Type}})
in
#"Changed Type1"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @arnabmit ;
You could group by the Case ID column then add a condition column.
1. group by Case ID column.
2.expand the table.
3. add a condition column.
4.delete the Max column.and The final output is shown below:
In additonal , M language like below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgRiQwNVpVgdmIgTEFtCRUyMDKFqzFBEnIHYBCpiZA7TZYwi4gLE5lARY0szqC5zU2QRkBojkEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, Name = _t, Percent = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Case ID", Int64.Type}, {"Name", type text}, {"Percent", Percentage.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Case ID"}, {{"Max", each List.Max([Percent]), type nullable number}, {"a", each _, type table [Case ID=nullable number, Name=nullable text, Percent=nullable number]}}),
#"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"Name", "Percent"}, {"a.Name", "a.Percent"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded a", "Flag", each if [a.Percent] = [Max] then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Max"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"a.Percent", "Percent"}, {"a.Name", "Name"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Percent", Percentage.Type}})
in
#"Changed Type1"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much! This is perfect!
NewStep=let a=Table.Buffer(Table.Group(PreviousStepName,{"Case ID","Name"},{"n",each List.Max([Percent])})) in Table.AddColumn(PreviousStepName,"Flag",each Byte.From([Percent]=a{[Case ID=[Case ID],Name=[Name]]}[n]))
Thanks a lot for your reply!
It gave me all 1 in the Flag column. Did I do something wrong?
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Case ID", Int64.Type}, {"Name", type text}, {"Percent", type number}}),
#"Add Column" = let a=Table.Buffer(Table.Group(#"Changed Type",{"Case ID","Name"},{"n",each List.Max([Percent])})) in Table.AddColumn(#"Changed Type","Flag",each Byte.From([Percent]=a{[Case ID=[Case ID],Name=[Name]]}[n]))
in
#"Add Column"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.