Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.