Need a Unique Location Number

[ronblaisdell]

So, I have a table of data where the "Unit Number" field may contain the same number, but these are separate locations. Trying to come up with a way to properly identify these so they may later be identified as distinct locations in the PowerPivot table.

Sample data:


I have tried a number of techniques, but I am "stuck." Ideally, since I don't want all the "1" locations to collapse in the PowerPivot, I'd like to create a new column that indicates a Unit Number of 1.0, 1.1, 1.2, 1.3, etc depending on the number of occurrences. I'm attaching a copy of the actual data file as well.

Any help would be appreciated.

 

[Ken Puls]

How's this?

I just spun off a unique dimension, numbered it, divided it by 100, merged it back to the main table, then added it to the original number. I think that should do what you need.

 

[ronblaisdell]

Wow, interesting solution! Not even vaguely close to where I was going... ah, so many tricks to learn...

Thanks!

 

[ronblaisdell]

Close!

So I spend some extended time examining your solution versus the path I was traversing. I realized where I made my mistake!