Importe row number from an excel file

[AFra]

Hi all, 

I have imported a folder with 150 excel files. I would like to import also the ROW NUMBER for each line of each excel file, so that I get an index column such as : 

EXCEL FILE	ROW NUMBER
001	1
001	2
001	3
002	1
002	2
002	3
003	1
003	2

and so on. 

Any idea on how I do this? 

Thanks in advance, 

Ana

[ImkeF]

Hi @AFra ,
if you use the Import from folder - functionality then you should have a query where you transform the data from a sample file. This query is "married" to an automatically generated function which is applied to every row/file within your folder before expanding all the values.

Go to this query and add an index column in there.
This will create what you've requested.

[AFra]

Hi @ImkeF , I tried what you suggested, but it doesnt fully work 😕 

I have an index column, but it displays a unique number per excel file. For each file (in the picture 11 Angles and ABC are files) I would need an index, from 1 to 200 (because each file contains 200 rows). 

Any ideas?  

[ImkeF]

Hi @AFra ,

yes, that's not the right place to add the index column. 

It has to sit in the function that your'e executing in the step before:

Find out where it comes from and add the index in there.

[AFra]

Thanks a lot @ImkeFfor your help, but I don't understand what you mean by "it has to sit it the function". I tried to execute the index function before in the steps, but it doesn't change anything. would you be kind enough to show me how to do it? here's a sample of the files (they should be in a folder) : https://we.tl/t-xJiXZIVMvt

[ImkeF · Accepted solution]

Hi @AFra ,

sure, here is the screenshot:

And also link to file below: 

[AFra]

Thanks a 10000 times! 

[AFra]

Hi @amitchandak, thank you for your response. 

I was wondering if there is any possibility to create an index column based on the folder name, so that the count of rouws starts at 1 at every change of name folder. Do you know if it's possible? 

[amitchandak]

@AFra , I think @ImkeF  , may provide a solution for this

@ImkeF , can you help with this

[amitchandak]

@AFra , You might have to create a new column of row number in excel for that. As in the case of folder, transformation is post-merge

In power bi You can add index column https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi