Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How can I add a unique identifier column to an unpivoted table?

I have a table with repeat primary key values due to being unpivoted, shown below

 

What I want to do is add a unique identifer that is NOT AN INDEX COLUMN. This is a sales table, so we might sell to an account more than one time, so the account code might actually have 12345 and upload two different times, on two different dates. An index column alone will not identify this

 

jsauerla_0-1616715609216.png

 

Objective: A unique identifier column that I can use to identify each account we sell to so I can use that in measures without measures being confused which account code I'm referring to because they repeat.

 

10 REPLIES 10
Keshav5
Regular Visitor

Hi Ashish,

Thanks. Do I create a newmeasure then?

Yes.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

What do you ultimately want to calculate?  Please share the final expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur I want to calculate first the working days of how long it took to get from one process step to the next. Then, I want the average working days for each account code. While it is unpivoted and has repeating values, I have no idea how to achieve this.

Hi,

To calculate working days (i.e. exclude weekends and public holidays) row wise, I think would be easier in the Query Editor.  Someone else will help you with this please.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

This is helpful. I understand what you need now, but I doubt a unique identifier is your answer. You need a way to get the difference between the value in one row and the value in the previous row (to get the working days for each task). I would look at something like this: https://community.powerbi.com/t5/Desktop/Difference-between-two-rows/td-p/291588

viviank
Resolver I
Resolver I

Wouldn't the Account Code column work as the unique identifier? I'm not sure why you would need a separate identifier if the Account Code already uniquely identifies each account. There's no need to make things more complicated than needed.  Maybe I'm missing something in the question....

 

Anonymous
Not applicable


@viviank wrote:

Wouldn't the Account Code column work as the unique identifier? I'm not sure why you would need a separate identifier if the Account Code already uniquely identifies each account. There's no need to make things more complicated than needed.  Maybe I'm missing something in the question....


@viviank The account code would not work, as we can sell to an account more than once. Say we sell something to them Jan 1st 2021, and then, we sell to them again Jan 15th. The account code would be repeated 10 times, as there are 5 process steps in the data, and 2 times we sold to them. So when I go to model the data, power BI would get confused as to which account code set to refer to for any analysis

Hi! Where you able to figure out the solution to this problem? I am stuck with a similiar situation. I have data for 138 individuals with an unique index ID for each.  However, after unpivoting some columns to arrange data, the index ID has multiplied for each individual and now when I am visualizing data in PowerBI, I am not getting the unique count but rather the row count. Any ideas on how I can resolve this?

Hi,

Use the DISTINCTCOUNT() function.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.