# How to create a Row number in Power Pivot



## nikkollai (Apr 6, 2015)

Hello, 

This was supposed to be a very simple task but i have exhausted all i  knew in PowerPivot and that's way decided to reach out to the pros. 
If you take a look at the file attached you will see that in order to  achieve a asymmetric data transfer from input table into pivot table  most like we will need to create row numbers in PowerPivot. However,  doing this in PowerPivot is not so easy. 

I will appreciate any ideas.

File can be downloaded here: http://1drv.ms/1DFbzU0

https://onedrive.live.com/embed?cid=00E81EAEA2895AD5&resid=e81eaea2895ad5%21578&authkey=AP4wUPX4Xm5RjlY


----------



## scottsen (Apr 6, 2015)

You would really need to add the row number before you got to Power Pivot.  Depending on your data source, your best option might be to use Power Query, which can add an "index column", which is just a row #.


----------



## nikkollai (Apr 6, 2015)

*scottsen* 
You think you could download that file and show me show to do this i am not good with Power Query just yet. 

This is the image of what i am trying to achieve. 

https://onedrive.live.com/?cid=00E81EAEA2895AD5&id=e81eaea2895ad5!578&v=3

Tsk, Nick


----------



## nikkollai (Apr 6, 2015)

*scottsen*

I should have mentioned that i am not querying any data. Data gets inputted and processed in the same worksheet.


----------



## scottsen (Apr 6, 2015)

If all the data is sourced from tables in the same Excel sheeet... why not just add a row number there?


----------



## nikkollai (Apr 6, 2015)

Very good argument and originally i made it so but my boss wants it gone...


----------



## scottsen (Apr 6, 2015)

In that case, I believe your only answer would be Power Query.  Should be pretty straight forward:

* On Power Query Tab, with your table selected, choose "From Table" to bring your data in
* On Power Query window, on the Add Column tab, choose "Add Index Column"
* File, Close and Load To... and choose "Only Create Connection" and "Add to Data Model"

Go enjoy your data in Power Pivot w/ the new row number.


----------



## nikkollai (Apr 6, 2015)

scottsen said:


> In that case, I believe your only answer would be Power Query.  Should be pretty straight forward:
> 
> * On Power Query Tab, with your table selected, choose "From Table" to bring your data in
> * On Power Query window, on the Add Column tab, choose "Add Index Column"
> ...



*scottsen* 	 , 

With PowerQuery it works like a charm but the problem is that among all the employees i am the only one that has it. The rest of the folks do not even have a remote idea what it  is and will not download it. There just has to be a way to do it in PowerPivot. Oh! How i wish there was an index column in PowerPivot as well.... 
Any other ideas ? I will take them all ...


----------



## scottsen (Apr 6, 2015)

Hrm.  Ya, unless you are the only person to refresh the data... that is going to be a challenge.

Other than "the order they come in" is there any way to determine which order the items should have been?  (by name, revenue, whatever?).

Otherwise, I think you are back to adding an index column in the original source data table.  Maybe you can just "hide" the column and your boss won't care...


----------



## nikkollai (Apr 6, 2015)

scottsen said:


> Hrm.  Ya, unless you are the only person to refresh the data... that is going to be a challenge.
> 
> Other than "the order they come in" is there any way to determine which order the items should have been?  (by name, revenue, whatever?).
> 
> Otherwise, I think you are back to adding an index column in the original source data table.  Maybe you can just "hide" the column and your boss won't care...



HI, 
Well! Yes it has to come from PowerPivot. I am thinking may be to create another table only with numbers from 1 to 100 (there will never be more than 100 rows). and then somehow bring that column into my main table.  Not sure though how this can be done...  TOPN could be also used so the data is numeric. So far no luck in bringing that "1 to 100"  numbers column into my main table.


----------



## nikkollai (Apr 6, 2015)

Hello, 

This was supposed to be a very simple task but i have exhausted all i  knew in PowerPivot and that's way decided to reach out to the pros. 
If you take a look at the file attached you will see that in order to  achieve a asymmetric data transfer from input table into pivot table  most like we will need to create row numbers in PowerPivot. However,  doing this in PowerPivot is not so easy. 

I will appreciate any ideas.

File can be downloaded here: http://1drv.ms/1DFbzU0

https://onedrive.live.com/embed?cid=00E81EAEA2895AD5&resid=e81eaea2895ad5%21578&authkey=AP4wUPX4Xm5RjlY


----------



## scottsen (Apr 6, 2015)

So, RANKX would be what you want, but ... it has to RANK... something.  Like, revenue or... RAND().  But there is no way to RANK by "the order the data came in".   

Other than resorting to writing some VBA script to export the table and add a column of numbers that you then import into Power Pivot... I got nothing.


----------



## nikkollai (Apr 6, 2015)

scottsen said:


> So, RANKX would be what you want, but ... it has to RANK... something.  Like, revenue or... RAND().  But there is no way to RANK by "the order the data came in".
> 
> Other than resorting to writing some VBA script to export the table and add a column of numbers that you then import into Power Pivot... I got nothing.



What about TOPN () You think it can be used?


----------



## scottsen (Apr 6, 2015)

Not in any way I can think of.  How are rows ordered in the input table?  totally arbitrary?

How about a hidden SHEET, that references the main table... and adds the row #?


----------



## nikkollai (Apr 6, 2015)

scottsen said:


> Not in any way I can think of.  How are rows ordered in the input table?  totally arbitrary?
> 
> How about a hidden SHEET, that references the main table... and adds the row #?



That sounds like a good idea. 
Sorry to ask so many questions. How would i exactly do that.  Any chance you could help me with the way how to exactly bring that row# into the main table?


----------

