Solved: I can't append queries because of duplicated value...

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Jumpstart your career with the Fabric Career Hub

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.

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 c oincide 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!

@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

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

@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.	Product	Company	Entry_No_Company
01	Fish	Josh	01_Josh

Table: Item Ledger Entry Jane

Entry No.	Product	Company	Entry_No_Company
01	Meat	Jane	01_Jane

Table: Item Ledger Entry Mike

Entry No.	Product	Company	Entry_No_Company
01	Vegetable	Mike	01_Mike

Table: Item Ledger Entry David

Entry No.	Product	Company	Entry_No_Company
01	Fruit	David	01_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.

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)

Table1 Table2 Append 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

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.

@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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Get Help

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Learn More

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Find out more