RaqForum 41 No.
182 View  • 
     ...

In Excel, generate a row using every N columns of data

 
excel(115) SPL XLL(1)

In the following Excel worksheet, starting from the 2nd column, every N columns of data forms a group. Take N=2 as an example:

A

B

C

D

E

F

G

1

ID

Type 1

Count 1

Type 2

Count 2

Type 3

Count 3

2

1

a

640

d

290

a

3

2

d

12000

a

1900

f

6000

4

3

f

48000

f

3600

e

1600

5

4

c

46000

e

3100

b

1200

6

5

e

47000

c

3400

d

1400

7

6

b

64000

b

3600

c

1200

Our task is to transpose columns to rows: use every two columns of data in each row to generate a new row, that is, expand each existing row to three rows; keep the 1st column ID; and add a new 2nd column whose name is No and which contains ordinal numbers of the newly expanded rows:

A

B

C

D

1

ID

No

Type

Count

2

1

1

a

640

3

1

2

d

290

4

1

3

a

5

2

1

d

12000

6

2

2

a

1900

7

2

3

f

6000

8

3

1

f

48000

9

3

2

f

3600

10

3

3

e

1600

Use SPL XLL to enter the following formula:

=spl("=?.conj(ID=~(1),~.m(2:).group((#-1)\2).(ID|#|~))",A2:G7)

Picture3png

~.m(2:) means retrieving data from the 2nd column of the current row to the end. The group() function groups the data every 2 columns.

To group the data every 5 columns, just modify parameter 2 to 5.

Source: https://stackoverflow.com/questions/78449082/using-power-query-to-unpivot-table


excel(115) SPL XLL(1)
 
 •  182 View  •