cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

I can't append queries because of duplicated values - how to solve?

Hello everybody.

 

I'm stucked with this problem and I don't know if PowerBI can handle it or I have to do something different out of PowerBI.

 

Situation:

I have multiple different Queries in Dynamics NAV 2016 and I take this data through ODATA option in PowerBI.

These queries are from four different companies that are in one database. So, each company has its own Sales table, Item Ledger Entry table, etc.

 

 

What I want to get

I want to append data from each Item Ledger Entry table into one table so like this when I analyze data I can filter by company by using a filter "Company" in the dashboard.

 

Problem

When I append all four tables, I get an error saying that duplicated values were found in Entry No. column (primary key from Item Ledger Entry table in Navision).

 

PowerBI is right. Each company has this table with different values and it's normal that "Entry No." can coincide among companies.

 

How I've thought to solve it but it doesn't work

 

So I thought, well, if "Entry No." can coincide, I create a custom column concatening "Entry No." + COMPANY.

 

Solved!

 

No...

 

When I append queries, it keeps appearing the error message because it keeps taking "Entry No." as primary key and not my custom column as primary key!!

 

My assumption

 

I guess that PowerBI takes "Entry No." as primary key because in Navision it's the primary key in Item Ledger Entry table, so, here is the problem. How can I do, so that when I append queries my custom column is taken (as "primary key") instead of default primary key (Entry No.)?

 

 

 

 

If you have any doubt don't hessitate to ask. Help gurus!

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Supplier
Solution Supplier

Re: I can't append queries because of duplicated values - how to solve?

@powerbirino

 

this is exactly what I mentioned previously: "and this is not allowed for columns from one of the sides of a one-to-many relationship

 

The Entry no. column is used in some relationship. You have to use "Entry no company" column in relationship, because Entry no. has duplicates as described in error message.

 

Regards.

Pavel

View solution in original post

7 REPLIES 7
Highlighted
Solution Supplier
Solution Supplier

Re: I can't append queries because of duplicated values - how to solve?

Hi @powerbirino3,

 

isn't it just about redefining relationship between those tables? I mean to replace "Entry No." column by "Entry No. + Company" column in Relationship pane and corresponding column from the other table?

 

I think your approach is right, I have also used this method in certain cases.

 

Regards.

Pavel

Highlighted
Frequent Visitor

Re: I can't append queries because of duplicated values - how to solve?

@PavelR

 

These four tables named "Item Ledger Entry + Company" aren't related because it's nonsense, besides, I think that I tried and it gave an error.

 

What I'd like is having these four tables in one table but the problem is that PowerBI takes as primary key the primary key from NAV's table (Item Ledger Entry) when joining them, and I want it to take my custom column.

 

Example:

 

 Table: Item Ledger Entry Josh

Entry No.ProductCompanyEntry_No_Company
01FishJosh

01_Josh

 

Table: Item Ledger Entry Jane

Entry No.ProductCompanyEntry_No_Company
01MeatJane01_Jane

 

Table: Item Ledger Entry Mike

Entry No.ProductCompanyEntry_No_Company
01VegetableMike01_Mike

 

Table: Item Ledger Entry David

Entry No.ProductCompanyEntry_No_Company
01FruitDavid01_David

 

 

 

When I append David, Mike and Jane tables to Josh table, it automatically takes "Entry No" as primary key (it is in NAV), and then it gives the error saying duplicated values, but what I want is to append them by Entry_No_Company. Appending queries doesn't give the option to select "by a column" and I think merging them isn't the correct option.

Highlighted
Solution Supplier
Solution Supplier

Re: I can't append queries because of duplicated values - how to solve?

Hi @powerbirino3,

thank you for clarification. I have never used Odata datasource yet, so maybe I do not know what restrictions it have. But in Power BI documentation is said:

 

In Power BI Desktop, you can connect to an OData feed and use the underlying data just like any other data source in Power BI Desktop.

 

So when I use Enter data as data source I can append queries even with duplicate rows (if i don't have any relationship on Append table and ID column)

 

Table1Table1Table2Table2Append tableAppend table

 

 

 

I don't get the issue about primary key as you described. In Power BI you do not specify primary key anywhere.

 

Regards.

Pavel

Highlighted
Frequent Visitor

Re: I can't append queries because of duplicated values - how to solve?

Hi @PavelR

 

Now I'm able to show you the error message

 

 

 

Translation is:

 

"Apply changes to the query

 

Item Ledger Entry

The column "Entry No" from Item Ledger Entry contains a duplicated value '30765' and this is not allowed for columns from one of the sides of a one-to-many relationship or for columns that are used as primary key of one table."

 

They aren't related, so the problem is about the primary keys and I don't see any option in PowerBI about primary keys.

 

Odata behaviour may be different of Entering data directly.

 

 

Highlighted
Solution Supplier
Solution Supplier

Re: I can't append queries because of duplicated values - how to solve?

@powerbirino

 

this is exactly what I mentioned previously: "and this is not allowed for columns from one of the sides of a one-to-many relationship

 

The Entry no. column is used in some relationship. You have to use "Entry no company" column in relationship, because Entry no. has duplicates as described in error message.

 

Regards.

Pavel

View solution in original post

Highlighted
Frequent Visitor

Re: I can't append queries because of duplicated values - how to solve?

@PavelR

 

It was that!! It was about relating correctly with the new columns instead of using the older ones. I mean, Entry No + Company instead of only Entry No.

 

Thank you!!

Highlighted
Solution Supplier
Solution Supplier

Re: I can't append queries because of duplicated values - how to solve?

You are welcome @powerbirino 🙂

 

Regards.

Pavel

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors