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

v-rzhou-msft
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
v-rzhou-msft
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors