cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KiranGupta15
Frequent Visitor

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.

 

 

ServerRiskRankCount
Server1102
Server1102
Server1203
Server1203
Server1203
Server1501
Server1604
Server1604
Server1604
Server1604
Server251
Server2403
Server2302
Server2602
Server2302
Server2501
Server2403
Server2602
Server2403
2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@KiranGupta15 - If I understand correctly:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

RicoZhou
Community Support
Community Support

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.

4.png

Duplicate the table, and group by then merge two tables. 

One step you can get the counts.

1.png

Then merge two tables by Sever column.

2.png

Result:

3.png

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. 

View solution in original post

3 REPLIES 3
RicoZhou
Community Support
Community Support

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.

4.png

Duplicate the table, and group by then merge two tables. 

One step you can get the counts.

1.png

Then merge two tables by Sever column.

2.png

Result:

3.png

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
Community Champion
Community Champion

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
Super User
Super User

@KiranGupta15 - If I understand correctly:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.