Adding a key to a table

[RenaG]

Hi,

I have a PBIX that contains several tables that were imported from Excel files.  Each week I append data to these tables.  I didn't anticipate duplicates within the table.  The main key is a pat_ID field.  I would like to add the RunDate value to the key to alleviate duplicates. Is there a way to do that on existing tables?

TIA,

Rena

[scottostanek]

Simple suggestion: In your report, model view, edit the query to your Excel file. With it displayed  click the table button (top left of grid) and look for add index. This separates it from your data and makes it a per row --letting you have duplicated pat_id fields without complaint. It then falls to you how to group and handle those results.

 From 0 gets 0,1,2,3 etc  

Does that do what you are looking for?

[RenaG]

@scottostanek Thank you for your reply.  This isn't want I am looking for.  To maybe explain it better, I am looking to create a composite key that contains the pat_ID and the RunDate.  That will make each record coming in unique.  I hope that makes more sense.

[v-tianyich-msft]

Hi @RenaG ,

First, in power bi, table visual hides the exact same rows. So I'm guessing that the duplicates you describe could mean something else, such as the duplicate RunDate shown below, which needs to show the latest RunDate:

Please feel free to correct me and provide more information if I have misunderstood you!

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[RenaG]

Thank you for your quick reply.  Here is some more info that may be helpful.

Each week I run a report that pulls records YTD.  So a patient who appeared last weeks' results most likely will appear in this weeks' results. I checked the result file and there are no duplicates within that result.  But when I appended this week to the Master (which contains last weeks' data) and did an Apply and Close then I got the error that there were duplicates. I thought the tables had been created with a key of Pat_ID and RunDate (another person created the framework of this dashboard but has since then left the company). Now I am tasked with getting it to work going forward.  All of the Master tables will be updated this way each week so I am sure to run into this on all of them.  Is there someplace I can go in the Applied Steps and indicate that the keys need to be on Pat_ID and RunDate?  I looked at the Model View and can't tell that the Pat_ID is the key on this particular table so I don't know where or how that is set.

I hope this all makes sense.

Any thoughts?

[Greg_Deckler]

@RenaG Potentially. You could probably create a function or otherwise extract the file creation date or what not and include that as a column in your tables. You could then create a unique key from that and your pat_ID field. Specifics are going to vary. Are you using a Folder query?

[RenaG]

@Greg_Deckler I was wondering if you had any other thoughts on this question. I can create a composite column with the pat_id and run date.  I don't know how to indicate that field is the unique key.  Can you explain that process?  Thanks!

[RenaG]

@Greg_Deckler Thank you for your reply.  I am a novice PowerBI developer so I don't understand your solution. Are you suggesting that in the Applied Steps on each table, somewhere at the beginning I create a column that contains the data from the pat_id and the run date? If so, how do I set that as the key for the table?