How to create a Row number in Power Pivot

[nikkollai]

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]

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]

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]

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]

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?