Numerate each individual row inside PowerPivot

[nikkollai]

Hello,

I have relatively easy question. My project requires that i numerate each individual row that contains some kind of value. My goal is to achieve the result as shown in forth column Numeric List

Values	Values2	Values3	Numeric List
Sample	Sample	Sample	1
Sample	Sample	Sample	2
Sample	Sample	Sample	3

<tbody>
</tbody>

Thank you, N -

 

[scottsen]

Short Answer: Use Power Query.

There are longer available answers, but let us start there

 

[nikkollai]

Hi Scottsen,

Thank you for your reply. At this point i would take that longer answer.
The list really has to be in PowerPivot only.
I was breaking my head all day yesterday ... what could be easier than to numerate your rows... but in PowerPivot it's very challenging.


N -

 

[scottsen]

In that case, it will probably help to know something about both your scenario and your data. If you can't use PQ, and can't add the column to your raw data...

Your best best will probably be RANKX. However, it behaves weird on "ties". To work around that, you end up doing some... trickery to avoid ties.

 

[nikkollai]

I really have nothing in my dataset to run rankx on. No dates, no numeric values only text strings. Any idea how to use rankx with text strings.
N

 

[scottsen]

You can rank against RAND() if it's okay for the order to switch on each refresh.

 

[nikkollai]

Scottsen,

Unfortunately, RAND() is not gonna work i need the date to be sorted numerically once data gets transferred into pivot table.
In regular EXCEL it's a piece of cake with ROW function but no such obvious solution in Power Pivot.

N -

 

[Matt Allington]

The list really has to be in PowerPivot only.

Why is this? power query is simply a data load and shaping tool. The end result will be in Power Pivot, not Power Query.

You also don't mention what logic you will use to work out the numbering order. If for some reason you truly can't use Power Query, you could try to use something like this ranked value example here.

https://support.office.com/en-gb/ar...17d-b4c7-7528febcef97?ui=en-US&rs=en-GB&ad=GB