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.
When I want to create calculated table using NATURALLEFTOUTERJOIN function I get the error message refering to column FGItemDescriptionKey (one which I use for joining tables) with message "The column with name FGItemDescriptionKey already exists in 'Table' Table. There is no table with name 'Table' in my data model. When testing my expression in DAX studio on same datamodel query run without problems. Does sombody can advice me on the solution of the problem?
Is there any update on this bug?
If I am not mistaken the "The column with name FGItemDescriptionKey already exists in 'Table' Table." error occurs if you join two tables with common column names that are not part of the join condition (probably as there is no relation defined between those columns).
NATURALINNERJOIN(SELECTCOLUMNS(table1, ...); SELECTCOLUMNS(table2, ...))
will probably solve your problem (either drop them or give them another name).
If you have some SQL knowledge: The same can happen if you join two tables using SQL, in which case giving the tables an alias helps.
Aides, in my experience, in Power BI the error also occurs if you join two tables with column names that are "part of the join condition".
This bug has not been fixed yet, am I correct?
Okay, I've looked into it.
The NATURALLEFTOUTERJOIN and NATURALINNERJOIN functions only seem to work in Power BI if the following 2 conditions are met:
If one of the above conditions is not met, the join will result in an error message.
This behaviour a) is totally different from Microsoft's description, and b) is very counter-intuitive and to some extent defeats the purpose of a join.
Fortunately, there's an easy way to work around this, simply by renaming one of the 2 colums.
I'm just getting started in Power BI and came across this issue today when trying to merge the results of a SQL direct query and an Excel file. Initially I couldn't find the merge button (in Power BI Desktop March 2018 Report Server version), so thought I'd try DAX. I ended up renaming the field in one of my sources, which resolved the problem, but given how counterintuitive this is, you'd think the error message would at least offer a tip as to how to best proceed in this situation.
The journey begins....
Hi,
I'm facin gthe same issue this morning. Trying to join two table : 1 from Direct Query, 1 from Excel. There is 1 column with the exact name, but the same error "No common join column detected"
I also tried to create new table inside Power BI desktop, for example Empl and Salary with 1 same column name in each table, lets say "EmpId" in table Empl and "EmpId" in table Salary.
Write this :
Then error " The column with the name "EmpId already exists in "Table" table." Why "table" anyway, I already put "EmpSalary" for my new tablename.
Well, from October 2018 to May 2019, I guess this problem still not yet solved.
Thanks,
Hi,
So, I try the longer way, thought there is some field order issue.
with this :
It only return me a new error :
An incompatible join column. [Branch Id] was detected. NATURALLEFTOUTERJOIN doesn't support join by usin gcolumns with different data types or lineage"
I confirmed I have same data type for my EmpID feld, so it leave me with lineage. What is lineage ?
My data is only like this :
EmpId Name
1 | Axel |
2 | Alex |
3 | Mike |
4 | Jordy |
EmpId Salary
1 | 330000 |
2 | 500000 |
Thanks,
I found this and work for me!
DAX Fridays! #114: NATURALINNERJOIN lineage error - YouTube
I faced the same issue, did the same (long path) and got the same result, another error.
Hello
Did you get any solution for the issue you are facing ?
Did you go with the Merge query option ??
PY
Where are you entering that formula? In a custom column or measure? If that is the case, I don't think you can use NATURALLEFTOUTERJOIN that way, it has to be part of a DAX formula that does not result in table. I could be 100% wrong.
I am enetring the formula in the new feature of PowerBI Desktop "Create Caculated Table" where accoring to documentation naturalleftouterjoin should work. https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-calculated-tables/
OK, I have tested this. Seems like there are more problems here. When I have a relationship defined between "ItemID" columns, I get the error that you get with both NATURALINNERJOIN and NATURALLEFTOUTERJOIN. If I remove the relationship and even though I have "ItemID" named the same in both tables, I get the error that there are no common columns between the tables even though the documentation indicates that it will join on common column names. I tried both 1:1 and 1:* relationships. Also with Single and Both crossfilters.
With CROSSJOIN with or without a relationship, I get the error that ItemID already exists. I tried both 1:1 and 1:* relationships. Also with Single and Both crossfilters.
UNION works with a relationship or without a relationship.
Both of my ItemID columns were whole number. I also tried as text. Same results.
I would post this to the Issues area of the community site and see if you can get a resolution that way.
Thanks for advice! I did it. Can you vote for the idea that we get it solved faster? Thanks. Can you vote for an idea that we it can be solved faster? Thaks!
Voted for!
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |