Duplicate records preventing Appended Table populating

[Anonymous]

I have 2 tables from Sharepoint Issue Tracker Lists.

They both Have ID columns which are effectively the Record number of the list, which is fine as a primary key for one list, but useless for a primary key when you want to append them, as they are from different lists and they can house the same value.

But when i do an append tables i get an error about duplicate primary Key records in the ID field.

Note there is a field in each table called GUID which are definitely unique.

Either way, the end result is that i need all records from both tables no matter what the ID value is.

Anyone know the easiest way to achieve this?

Thanks in Advance

[Anonymous · Accepted solution]

Thanks Ross.

I found the spot and noticed that it was using a mixture of FileSytemObject and ID fields for the relationships.

I just changed them all to the GUID field as per below. I don't know if this is the right way to do it but it seems to have done the trick.

I'm thinking that these dont need to be true relational tables as i just want the records to append. Sorry i'm not a DB person, so i'm fumbling through here a bit.

Note: I grabbed 3 'identical' tables and then created a new append table.

[Anonymous]

I'd create a new column in both tables called "Key" which is the ID column concatatinated with a string that relates to your table.

For example you might have a table called "Kittens" and a row has an ID of 1, so your Key column might be "Kit_1"
In your next table, puppies, you would do something similar so a row with an ID of 1, would have a key of "Pup_1".

I'm sure you get the idea.

[Anonymous]

Thanks Ross. Probably should have added That I’m ok with finding or creating a unique field but my problem is how do I make Power BI use a different field as the primary? I’ve been looking everywhere and can’t find out how?

[Anonymous]

To answer your direct question. You can use the Table.Key function for that

https://docs.microsoft.com/en-us/powerquery-m/table-addkey

However in reality, its likely your table relationships that a causing you some pain.  Firstly just delete the relationship(s) that are causing the issue from that field, then apply your new power query changes that create the key field.  Once that imports correctly, set up your table relationship(s) based on that field again in the "Model" area of Power BI.

[Anonymous · Accepted solution]

Thanks Ross.

I found the spot and noticed that it was using a mixture of FileSytemObject and ID fields for the relationships.

I just changed them all to the GUID field as per below. I don't know if this is the right way to do it but it seems to have done the trick.

I'm thinking that these dont need to be true relational tables as i just want the records to append. Sorry i'm not a DB person, so i'm fumbling through here a bit.

Note: I grabbed 3 'identical' tables and then created a new append table.

[Anonymous · Accepted solution]

Yes thats a good solution. Requires lets data manipulation so its a better solution overall.  You are doing it right.

[Anonymous]

Great, thanks for your help Ross