Forum Discussion
Counts in a another column
Hi All,
I m new to pwer BI, please help on below.
count in a new column based on other 2 columns.
Appreciate your support here.
| Server | RiskRank | Count |
| Server1 | 10 | 2 |
| Server1 | 10 | 2 |
| Server1 | 20 | 3 |
| Server1 | 20 | 3 |
| Server1 | 20 | 3 |
| Server1 | 50 | 1 |
| Server1 | 60 | 4 |
| Server1 | 60 | 4 |
| Server1 | 60 | 4 |
| Server1 | 60 | 4 |
| Server2 | 5 | 1 |
| Server2 | 40 | 3 |
| Server2 | 30 | 2 |
| Server2 | 60 | 2 |
| Server2 | 30 | 2 |
| Server2 | 50 | 1 |
| Server2 | 40 | 3 |
| Server2 | 60 | 2 |
| Server2 | 40 | 3 |
KiranGupta15 - If I understand correctly:
Count = COUNTROWS(FILTER('Table',[Server]=EARLIER([Server])&&[Risk]=EARLIER([Risk])))Hi KiranGupta15
If you want to get the count in another column by Power Query and keep your data model you may try my way.
I build a table like yours to have a test.
Duplicate the table, and group by then merge two tables.
One step you can get the counts.
Then merge two tables by Sever column.
Result:
M Query is as below, you can copy this in Advanced Editor.
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstMlTSUTI0UIrVwStgRLqAKbqAGYUCRiBD0fgm6AqM0QUwjMBQYYougGEohhkgFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, RiskRank = _t]), #"Merged Queries" = Table.NestedJoin(Source, {"Server", "RiskRank"}, Table2, {"Server", "RiskRank"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Count"}, {"Table2.Count"}) in #"Expanded Table2"Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
KiranGupta15 - If I understand correctly:
Count = COUNTROWS(FILTER('Table',[Server]=EARLIER([Server])&&[Risk]=EARLIER([Risk])))Hi KiranGupta15
you can do it with Power Query like this:
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstMlTSUTI0UIrVwStgRLqAKbqAGYUCRiBD0fgm6AqM0QUwjMBQYYougGEohhkgFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, RiskRank = _t]), #"Inserted Merged Column" = Table.AddColumn(Source, "Merged", each Text.Combine({[Server], [RiskRank]}, ""), type text), #"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"Merged"}, {{"Count", each Table.RowCount(_), Int64.Type}}) in #"Grouped Rows"With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)Hi KiranGupta15
If you want to get the count in another column by Power Query and keep your data model you may try my way.
I build a table like yours to have a test.
Duplicate the table, and group by then merge two tables.
One step you can get the counts.
Then merge two tables by Sever column.
Result:
M Query is as below, you can copy this in Advanced Editor.
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstMlTSUTI0UIrVwStgRLqAKbqAGYUCRiBD0fgm6AqM0QUwjMBQYYougGEohhkgFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, RiskRank = _t]), #"Merged Queries" = Table.NestedJoin(Source, {"Server", "RiskRank"}, Table2, {"Server", "RiskRank"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Count"}, {"Table2.Count"}) in #"Expanded Table2"Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
