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]

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]

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]

scottsen

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

 

[scottsen]

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

 

[nikkollai]

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

 

[scottsen]

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]

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.

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]

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]

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.