How to make a grouping index in single column download

mrgramm · Jan 6th 2023

Data is downloaded in a single column.

Each record ends with a number. With excel formula I can give each line of each record a grouping number.

Can not seem to replicate it in power query. IF(ISNUMBER(A2),C2+1,C2).The above M code will put a 0 in the first row and then error.

I have attached a worksheet with the query.

I could add the grouping column before starting the query but I would rather not.

Thanks for any help you can give.

Table.AddColumn(#"Added Index1", "GroupBy", each if [Index.1]= -1 then 0 else

if {[Index.1]}[Custom]= "Text" then {[Index.1]}[GroupBy] else

{[Index.1]}[GroupBy]+1)

**Carim** · Jan 6th 2023

Hi,

Below is a link which explains how to create a group index with Power Query :

It is possible to generate a group index (unique row ids) by one or many groups in Power Query. Here is 2 solutions with M language and R.

datacornering.com

Hope this will help

mrgramm · Jan 6th 2023

I only have one column and each row is different. Each record contains a line with a number. Number of lines per record vary.

**Carim** · Jan 7th 2023

Hi again,

Attached is your test file, with my interpretation of the flat database that will probably facilitate all your subsequent tasks ...

Hope this will help

mrgramm · Jan 7th 2023

Thank You.

**Carim** · Jan 8th 2023

You are welcome

**Carim** · Jan 8th 2023

Hi again,

Forgot to mention a Power Query transformation would rely on the fact that each record set has exactly 5 rows...

The key is that it’s a fixed constant for each record.

see following example :

Participate now!