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

[Krishna_Newuser]

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)

Current Data:

ChannelID	ConversationId	TotalParticipantCount	TxtDt
19:c97b993bbf7d46b6a580bd7efd085ceb@thread.skype	b56428a2-4f92-4e63-9dc1-fdc86f99c1cd	2	12/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype	5b05b549-9778-4ce8-8bc7-b6eab3ef8d33	3	12/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype	5b05b549-9778-4ce8-8bc7-b6eab3ef8d33	3	12/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype	5b05b549-9778-4ce8-8bc7-b6eab3ef8d33	3	12/30/2024
19:8a7aa6396ea543d991384b9086c0528f@thread.skype	b3192630-95b7-4212-87b9-fb6e87620ae1	1	12/31/2024
19:8a7aa6396ea543d991384b9086c0528f@thread.skype	b3192630-95b7-4212-87b9-fb6e87620ae1	1	12/31/2024
19:03344baf465c4671b07532a955ca0a5b@thread.skype	74fbd791-3a2b-43e4-ac9f-2fc58ec859fe	4	01-01-25
19:03344baf465c4671b07532a955ca0a5b@thread.skype	74fbd791-3a2b-43e4-ac9f-2fc58ec859fe	4	01-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	7259c700-9f95-4c6c-b4d2-2fd7f3c15cb1	5	02-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	7259c700-9f95-4c6c-b4d2-2fd7f3c15cb1	5	02-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	7259c700-9f95-4c6c-b4d2-2fd7f3c15cb1	5	02-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	7259c700-9f95-4c6c-b4d2-2fd7f3c15cb1	5	02-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	1fb11813-ca07-4da9-aa77-6615d3c71cf8	3	03-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	1fb11813-ca07-4da9-aa77-6615d3c71cf8	3	03-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	04-01-25

Expected Result:

ChannelID	ConversationId	Count of Conversation ID	TotalParticipantCount	TxtDt
19:c97b993bbf7d46b6a580bd7efd085ceb@thread.skype	b56428a2-4f92-4e63-9dc1-fdc86f99c1cd	1	2	12/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype	5b05b549-9778-4ce8-8bc7-b6eab3ef8d33	3	3	12/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype	5b05b549-9778-4ce8-8bc7-b6eab3ef8d33	3	3	12/30/2024
19:ae668e32d2bf42dbb9a174eb35643892@thread.skype	5b05b549-9778-4ce8-8bc7-b6eab3ef8d33	3	3	12/30/2024
19:8a7aa6396ea543d991384b9086c0528f@thread.skype	b3192630-95b7-4212-87b9-fb6e87620ae1	2	1	12/31/2024
19:8a7aa6396ea543d991384b9086c0528f@thread.skype	b3192630-95b7-4212-87b9-fb6e87620ae1	2	1	12/31/2024
19:03344baf465c4671b07532a955ca0a5b@thread.skype	74fbd791-3a2b-43e4-ac9f-2fc58ec859fe	2	4	01-01-25
19:03344baf465c4671b07532a955ca0a5b@thread.skype	74fbd791-3a2b-43e4-ac9f-2fc58ec859fe	2	4	01-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	7259c700-9f95-4c6c-b4d2-2fd7f3c15cb1	4	5	02-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	7259c700-9f95-4c6c-b4d2-2fd7f3c15cb1	4	5	02-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	7259c700-9f95-4c6c-b4d2-2fd7f3c15cb1	4	5	02-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	7259c700-9f95-4c6c-b4d2-2fd7f3c15cb1	4	5	02-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	1fb11813-ca07-4da9-aa77-6615d3c71cf8	2	3	03-01-25
19:hLHSyoaUCM2KYIdr5CQHpwvi-NZ1WP1cXN8mXf747rE1@thread.tacv2	1fb11813-ca07-4da9-aa77-6615d3c71cf8	2	3	03-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	6	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	6	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	6	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	6	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	6	04-01-25
19:2f3931dd64214224a683a84a564b90b0@thread.skype	1fb11813-ca07-4da9-aa77-6615d3c71cf8	5	6	04-01-25

[v-yaningy-msft · Accepted solution]

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.

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

[Krishna_Newuser]

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.

[v-yaningy-msft]

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

[Krishna_Newuser]

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.

[ahmedoye]

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.

[Krishna_Newuser]

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.