Index Column - separately for each file

[IPGeorgiev]

Hi all,

I have a dataset where the data source is a folder. This folder has multiple excel files with the same type of Raw data , but for different periods.

In order to achieve what I want I need the following - index column which runs from the begining for each file.

So for exmaple:

Raw data file 1 has 1000 rows

Raw data file 2 has 500 rows

Raw data file 3 has 670 rows

If I add a normal index column in PQ starting from 1 it will give me values from 1 to 2170

What I need is to have index formula run for each file separately  , so to say, so that at the end I will have the number 400 three times (as such row exits in all of the raw data files) but will have number 700 just once, since this is the case only for Raw data file 1.

Is this possible? It doesnt have to be necesserly with index formula of course.

Many thanks!

Best regards,

Ivan

[Greg_Deckler · Accepted solution]

Yes, edit the query for the sample file and add the index there. You want the Transform Sample File query. I have attached an example PBIX.

[john121]

Hi Greg,

Could you reupload the file as it is giving  data source error.

Thanks!