Add specific index column for each new file

[SimonSchoutz]

Hello everyone,

I´m quite new with the tool power query and i need some help with a presumable easy task, but i don´t know, how to solve it.

I have a directory with 300 files with about 1300 rows and 30 columns each. Now, i want to add to each file one column no. 31 with a number starting with 1 for the first file and 2 for the second file and so on ... The main problem is, after 8 files, the 9th file should start again with 1. My approach was to group all data according to their filename and add an index column, but this does not work, since index would count further after number 8.

The consolidated file should look like this:

File 1	1
...	...
File 1	1
File 2	2
...
File 8	8
File 9	1
...
File 9	1
File 10	2

Is there a way to solve this via power query? I assume, DAX won´t work due to the high amount of data.

Looking forward for every possible solultion, adding a simple column for each document by hand is quite a pain.

[ryan_mayu]

@SimonSchoutz 

you can create an index column

then create a custom column

=Number.RoundUp([Index]/8,0)

[SimonSchoutz]

Dear ryan_maru,

Thanks alot for your quick response.

Unfortunately, each file name is unique and not recurring.

Maybe, following picture describes the problem better:

Column "Anzahl" contains a table for each file listing all data for each file.

I want the index column to restart after counting to 8 independent from the data name.

Is there a way to reset index or set a limit value for the index?

Maybe, it is also a mistake to group all data according to the file names?

Kind regards,

Simon

[ryan_mayu]

even if the file name is unique, you can still use the M coding that i provided. What's the expected output based on the screenshot you provided?

[SimonSchoutz]

When i add the above mentioned code, i receive following output:

As you can see, the custom column still increases counting upwards, even when number 8 is obtained plus now 8 unique filenames have the same number:

Current result:

F1	1
F2	1
F3	1
F4	1
F5	1
F6	1
F7	1
F8	1
F9	2
F10	2
F11	2
F12	2
F13	2
F14	2
F15	2
F16	2
F17	3
...	...
F64	8
F65	9
F66	9

Expected result:

F1	1
F2	2
F3	3
F4	4
F5	5
F6	6
F7	7
F8	8
F9	1
F10	2
F11	3
F12	4
F13	5
F14	6
F15	7
F16	8
F17	1

Every 8 unique filenames, the index should be reset to 1 and increase by 1 for each following unique filename until it has counted to 8 to start again.

I hope, this gives a better explanation for my issue.

Let me know, if i should describe the issue with another example.

Kind regards,

Simon

[ryan_mayu · Accepted solution]

@SimonSchoutz 

pls try this

=if Number.Mod([Index],8) = 0 then 8 else Number.Mod([Index],8)

[SimonSchoutz]

Perfect, thats exactly, what i was looking for. Thanks alot for your support!

[ryan_mayu]

you are welcome