Error while Refreshing data on power bi Desktop

[Anonymous]

Hello,

I have a PowerBi desktop file connected to SQL Server (import data model)

its working fine for few months now.

today i tried to refersh the data and it says there's a duplicate in one of the TrxID entries (TrxID values should be unique since they are the primary key) i discussed this with the developer and he assured me the error and fixed it in the DB.

but again if i try to refersh it says the TrxID column has duplicates. 

here is a screen shot..

ERROR

what should i do?

appreciate your help

[Anonymous]

UPDATE:

if I refresh data i get 7 duplicate values in the transactions. i have the duplicate values in my data model, not in the data base. i just confirmed that. 

Now im getting crazy. why are there duplicates in my data model though the source dont have duplicates. ?

here is a screenshot from my db. as you notice the entries only appear once.

SC from DB

and here is my power bi report showing duplicates after the update. 

i suspected this is because of cached data, i cleared cache from <Options> but did not work. 

I appreciate you support gentelmen. 

[v-yiruan-msft]

Hi @Anonymous ,

Please refer this documentation to remove permission of SQL server data source and reconnect it. Later check if the issue still persist...

Best Regards

Rena

[Anonymous]

Hello @v-yiruan-msft 

Thank you for your responce.

the link to document is broken.

I have an update here:

i found a value that powerbi is considering as duplicate here is the image from my db:

the 00 in the starting of the TrxID is considered duplicate in powerbi

there are 7 values considered duplicates as per power bi that start with 00

NOTE: in powerbi the data type of TrxID is TEXT

what can i do about them?

[v-yiruan-msft]

Hi @Anonymous,

Sorry for bringing your inconvenience. Here is the correct link address. It seems when these values with prefix several 0 be removed automatically after import into Power BI... Later I will check the possible method to avoid it. Any progress I will update you.

Best Regards

Rena

[Anonymous]

Thank you for your support @v-yiruan-msft 

i qoute from your reply

"it seems when these values with prefix several 0 be removed automatically after import into Power BI... Later I will check the possible method to avoid it."

I think this is the problem. in sql the data type is navchar, and in powerbi it is TEXT . the prefix of Zeros is getting deleted automatically. 

how can I fix this.

Best

[v-yiruan-msft]

Hi @Anonymous ,

The prefix 0 may be automatically deleted in the "Change Type" applied step(see below screen shot). So try to find this applied step and delete it in Query Editor to check if the corresponding 0 can be displayed.

Best Regards

Rena

[Anonymous]

Hello Rena, @v-yiruan-msft 

Thank you for your sincere support, the issue is completely resolved. 
I also thank the other contributors for their genuine intrest to give advise.

Best

[amitchandak]

@Anonymous , open Data transformation mode/ edit query. Go to the table and try to delete the duplicate .

refer : https://radacad.com/remove-duplicate-doesnt-work-in-power-query-for-power-bi-here-is-the-solution

[Anonymous]

thank you for your reply. but your suggestion is not working. there is no duplicate in the db im 100% sure, niether its in the data model. delete duplicates wont work. 

any other suggestions?

NB: data updates daily in the server, and i have to refresh it in the powerbi model.

[mahoneypat]

Check for blank or null values too.  In Data View, when you highlight that column, does the # of distinct values equal the number of rows?  That's where I start when troubleshooting duplicates on key columns.  If needed, you can make a table visual with the values from the column and a measure that counts the rows and look for which one is >1.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

---

@mahoneypat wrote:

Check for blank or null values too.  In Data View, when you highlight that column, does the # of distinct values equal the number of rows?  That's where I start when troubleshooting duplicates on key columns.  If needed, you can make a table visual with the values from the column and a measure that counts the rows and look for which one is >1.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

---

Thanks for the suggestion, i did as you said, check my UPDATE

[Anonymous]

Thanks for the suggestion, i did as you said, check my UPDATE