Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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)
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.
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
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |