Counts in a another column

[KiranGupta15]

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

[Greg_Deckler · Accepted solution]

@KiranGupta15 - If I understand correctly:

Count = COUNTROWS(FILTER('Table',[Server]=EARLIER([Server])&&[Risk]=EARLIER([Risk])))

[Anonymous · Accepted solution]

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. 

[FrankAT]

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)

[Greg_Deckler · Accepted solution]

@KiranGupta15 - If I understand correctly:

Count = COUNTROWS(FILTER('Table',[Server]=EARLIER([Server])&&[Risk]=EARLIER([Risk])))