Reply
Krishna_Newuser
Helper III
Helper III
Partially syndicated - Outbound

How to count the rows of the particular Conversation ID in Power Query Editor

‎01-06-2025 08:37 AM

Hello,

I have the data which i will group the conversation ID, i just need to take the count of rows of the particular conversation ID in Power Query Editor. I have 40 Million data, One conversation ID will have 5 rows and another one will have 3 rows, we just need to update the count in the 1st row of each conversation ID.
Need help to calculate the count in the Power Query Editor.  

Note: Expected result shows the count rows (Count of Conversation ID)

Krishna_Newuser_0-1736181335213.png

 

Current Data:

ChannelIDConversationIdTotalParticipantCountTxtDt
19:c97b993bbf7d46b6a580bd7efd085ceb@thread.skypeb56428a2-4f92-4e63-9dc1-fdc86f99c1cd212/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype5b05b549-9778-4ce8-8bc7-b6eab3ef8d33312/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype5b05b549-9778-4ce8-8bc7-b6eab3ef8d33312/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype5b05b549-9778-4ce8-8bc7-b6eab3ef8d33312/30/2024
19:8a7aa6396ea543d991384b9086c0528f@thread.skypeb3192630-95b7-4212-87b9-fb6e87620ae1112/31/2024
19:8a7aa6396ea543d991384b9086c0528f@thread.skypeb3192630-95b7-4212-87b9-fb6e87620ae1112/31/2024
19:03344baf465c4671b07532a955ca0a5b@thread.skype74fbd791-3a2b-43e4-ac9f-2fc58ec859fe401-01-25
19:03344baf465c4671b07532a955ca0a5b@thread.skype74fbd791-3a2b-43e4-ac9f-2fc58ec859fe401-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv27259c700-9f95-4c6c-b4d2-2fd7f3c15cb1502-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv27259c700-9f95-4c6c-b4d2-2fd7f3c15cb1502-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv27259c700-9f95-4c6c-b4d2-2fd7f3c15cb1502-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv27259c700-9f95-4c6c-b4d2-2fd7f3c15cb1502-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv21fb11813-ca07-4da9-aa77-6615d3c71cf8303-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv21fb11813-ca07-4da9-aa77-6615d3c71cf8303-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf8504-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf8504-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf8504-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf8504-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf8504-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf8504-01-25

 

Expected Result:

ChannelIDConversationIdCount of Conversation IDTotalParticipantCountTxtDt
19:c97b993bbf7d46b6a580bd7efd085ceb@thread.skypeb56428a2-4f92-4e63-9dc1-fdc86f99c1cd1212/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype5b05b549-9778-4ce8-8bc7-b6eab3ef8d333312/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype5b05b549-9778-4ce8-8bc7-b6eab3ef8d333312/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype5b05b549-9778-4ce8-8bc7-b6eab3ef8d333312/30/2024
19:8a7aa6396ea543d991384b9086c0528f@thread.skypeb3192630-95b7-4212-87b9-fb6e87620ae12112/31/2024
19:8a7aa6396ea543d991384b9086c0528f@thread.skypeb3192630-95b7-4212-87b9-fb6e87620ae12112/31/2024
19:03344baf465c4671b07532a955ca0a5b@thread.skype74fbd791-3a2b-43e4-ac9f-2fc58ec859fe2401-01-25
19:03344baf465c4671b07532a955ca0a5b@thread.skype74fbd791-3a2b-43e4-ac9f-2fc58ec859fe2401-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv27259c700-9f95-4c6c-b4d2-2fd7f3c15cb14502-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv27259c700-9f95-4c6c-b4d2-2fd7f3c15cb14502-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv27259c700-9f95-4c6c-b4d2-2fd7f3c15cb14502-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv27259c700-9f95-4c6c-b4d2-2fd7f3c15cb14502-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv21fb11813-ca07-4da9-aa77-6615d3c71cf82303-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv21fb11813-ca07-4da9-aa77-6615d3c71cf82303-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf85604-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf85604-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf85604-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf85604-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf85604-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype1fb11813-ca07-4da9-aa77-6615d3c71cf85604-01-25
Labels:
Message 1 of 7
318 Views
0
1 ACCEPTED SOLUTION
v-yaningy-msft
Community Support
Community Support

‎01-06-2025 07:45 PM
Syndicated - Outbound

Hi, @Krishna_Newuser 

Thanks for @ahmedoye's reply. You can try his M code to solve your problem. If it's not fit your need, you can try the following method in Power Query Editor. Note that the results seem to be a bit different from yours, the output of the last few lines should be 6 instead of 5.

vyaningymsft_1-1736221421594.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7dXNaxUwDADw/+WdV9d8tYknYQgTdSgiTscOST+YiDieY7L/3r4xLxuItyk+aHsK/UGapGdnG7CnzWqYUcSsnUsUF83R65g9q7QRz64utsP7k+9fbi7H5mATUhjVMfG0dYxCyXqDNHvTMs0atL7CcG3AQ8qHmJE35we3mI9SdBB2jMnYI8yh8ghal5Ia3scksoSwJatVE7ehSaPVFGV40JjaiVYY/WeYenUvZCtWmLoZkHJY1tKyoM4Hb0ZgWCgnk6iJETDpevU0l6a1YPYBO+gOg8fDMhFz+OQijUuFyFUI3USaZ5cH1Vh5rmI1SOQYiWlw8mYz4Wyio6nY3IXx2hnSWiiPQF28On53883fH73Glx9f9K0cvT2+/HH9OZ18gg9voJ2e6NfTWblun8Mv9srb9a6JKoq1mlc6p8kqlNJScMfF9jqpgbTYpVJ2LO7Zf4qFGQAKlFbFrVbpbsm91lQKSKdWoU29GwKZ/goWJxlB7+sPAEZkL0qu7GvKrZEQ+X7T/CF1m1jeU3vqN9T5Tw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ChannelID = _t, ConversationId = _t, TotalParticipantCount = _t, TxtDt = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ChannelID", type text}, {"ConversationId", type text}, {"TotalParticipantCount", Int64.Type}, {"TxtDt", type date}}),
    GroupedRows = Table.Group(ChangedType, {"ChannelID", "ConversationId"}, {{"Count", each Table.RowCount(_), type number}}),
    MergedTables = Table.NestedJoin(ChangedType, {"ChannelID", "ConversationId"}, GroupedRows, {"ChannelID", "ConversationId"}, "GroupedRows", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "GroupedRows", {"Count"})
in
    ExpandedTable

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

How-to-count-the-rows-of-the-particular-Conversation-ID-in-Power.pbix
Message 4 of 7
243 Views
6 REPLIES 6
v-yaningy-msft
Community Support
Community Support

‎01-06-2025 07:45 PM
Syndicated - Outbound

Hi, @Krishna_Newuser 

Thanks for @ahmedoye's reply. You can try his M code to solve your problem. If it's not fit your need, you can try the following method in Power Query Editor. Note that the results seem to be a bit different from yours, the output of the last few lines should be 6 instead of 5.

vyaningymsft_1-1736221421594.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7dXNaxUwDADw/+WdV9d8tYknYQgTdSgiTscOST+YiDieY7L/3r4xLxuItyk+aHsK/UGapGdnG7CnzWqYUcSsnUsUF83R65g9q7QRz64utsP7k+9fbi7H5mATUhjVMfG0dYxCyXqDNHvTMs0atL7CcG3AQ8qHmJE35we3mI9SdBB2jMnYI8yh8ghal5Ia3scksoSwJatVE7ehSaPVFGV40JjaiVYY/WeYenUvZCtWmLoZkHJY1tKyoM4Hb0ZgWCgnk6iJETDpevU0l6a1YPYBO+gOg8fDMhFz+OQijUuFyFUI3USaZ5cH1Vh5rmI1SOQYiWlw8mYz4Wyio6nY3IXx2hnSWiiPQF28On53883fH73Glx9f9K0cvT2+/HH9OZ18gg9voJ2e6NfTWblun8Mv9srb9a6JKoq1mlc6p8kqlNJScMfF9jqpgbTYpVJ2LO7Zf4qFGQAKlFbFrVbpbsm91lQKSKdWoU29GwKZ/goWJxlB7+sPAEZkL0qu7GvKrZEQ+X7T/CF1m1jeU3vqN9T5Tw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ChannelID = _t, ConversationId = _t, TotalParticipantCount = _t, TxtDt = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ChannelID", type text}, {"ConversationId", type text}, {"TotalParticipantCount", Int64.Type}, {"TxtDt", type date}}),
    GroupedRows = Table.Group(ChangedType, {"ChannelID", "ConversationId"}, {{"Count", each Table.RowCount(_), type number}}),
    MergedTables = Table.NestedJoin(ChangedType, {"ChannelID", "ConversationId"}, GroupedRows, {"ChannelID", "ConversationId"}, "GroupedRows", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "GroupedRows", {"Count"})
in
    ExpandedTable

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

How-to-count-the-rows-of-the-particular-Conversation-ID-in-Power.pbix
Message 4 of 7
244 Views
Krishna_Newuser
Helper III
Helper III
In response to v-yaningy-msft

‎01-07-2025 03:22 AM
Syndicated - Outbound

Hello @v-yaningy-msft,

Thank you for the reply. If its showing 5 instead of 6 it will give the wrong result which will count for me will vary, is there anything else i can try.

Message 5 of 7
218 Views
0
v-yaningy-msft
Community Support
Community Support
In response to Krishna_Newuser

‎01-07-2025 09:15 PM
Syndicated - Outbound

Hi, @Krishna_Newuser 

Can you explain why you expect the result to be 5 and not 6, it looks to me like there are 6 rows of the same data.

Best Regards,
Yang
Community Support Team

Message 6 of 7
172 Views
0
Krishna_Newuser
Helper III
Helper III
In response to v-yaningy-msft

‎01-08-2025 09:56 AM
Syndicated - Outbound

Hello @v-yaningy-msft 

My bad, i checked the participant count instead of count, its working fine in power query editor.
Thank you so much for the help.

Message 7 of 7
144 Views
ahmedoye
Responsive Resident
Responsive Resident

‎01-06-2025 10:51 AM
Syndicated - Outbound

Here's a solution that should work. If it does work, please mark this as a solution so anyone with similar challenges can easily find it:

Solution:

In Power Query, Add a New Custom Column. Type the formula below in the Custom Formula Box:

Table.RowCount(Table.FindText(#"Changed Type", [ConversationId])).

Kindly note, that you can replace #"Changed Type" with the name of the Last step in your Query. If the name contains space, you must put the name inside double quotes and prefix the enclosed name with #. If the name does not contain spaces, just type the Name as it appears in your Query Steps.

Message 2 of 7
267 Views
0
Krishna_Newuser
Helper III
Helper III
In response to ahmedoye

‎01-07-2025 03:28 AM
Syndicated - Outbound

Hi @ahmedoye 
Thank you for the reply!!
I tried this step, it waited nearly 4 hrs, still data is loading hence i closed the PBI. Is there any steps which can help on it.

Message 3 of 7
214 Views
0
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Stop
View All
Top Solution Authors (Last Month)
View All
Top Kudoed Authors (Last Month)
View All