Creating a new column of labelled sequences

[Anonymous]

Hello everyone, 

I would like to create a column in Power Query M that is based on a previous column containing nothing or a string of text in its rows. The contents of my column actually indicate a certain sequence and I need to have a numbering system in another column that indicates how many As (see below) are in one sequence (the number should appear next to each A of the sequence). 

Example below: 

column1         column2 (the one I need)

A                     3
A                     3
A                     3


A                     2
A                     2

A                     3
A                     3
A                     3



A                     1


A                     2
A                     2

Moreover, my data also belongs to different categories, which should not be mixed. This means that if I have three As in a sequence but not from the same category, they should not be numbered as before. Example below: 

category       column1       column2

fruit               A                  2

fruit               A                  2

veg                A                  1

Before, the third A belonged to the sequence and we said that there were three As in that sequence. Now, there is one sequence with two As and another one with one A. Every sequence with 2 As from the same category should have a '2' in the new column etc. 

I have tried to group my data but it is too big (millions of lines) and it makes my computer crash. 

I would be very grateful if anyone could help me with this 🙂 

Thanks, 

Louise

[V-lianl-msft]

As @lbendlin  said, when I face this problem, I will choose to process the data at the data source instead of using power bi for data processing. Its biggest purpose is data visualization. The performance of directly processing the data source will be better than that in power bi.

[lbendlin]

You can do this but only if you add an index column.  If your data is millions of rows then the Power Query will be very, very slow (even with Table.Buffer). Rethink your requirement, and see if you can do this in the data source instead (but you still need the index column).