Forum Discussion

KiranGupta15's avatar
KiranGupta15
Frequent Visitor
5 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    KiranGupta15 - If I understand correctly:

    Count = COUNTROWS(FILTER('Table',[Server]=EARLIER([Server])&&[Risk]=EARLIER([Risk])))
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
FrankAT
Community Champion
5 years ago

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)