cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alfred
New Member

BUG: NATURALLEFTOUTERJOIN can't be used to create calculated table

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?

 

Calculated Table Bug.jpg

 



13 REPLIES 13
IanBates
Frequent Visitor

Is there any update on this bug?

Aides
New Member

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:

  1. The relationship between both tables has to already be defined, before the join is applied.
  2. The names of the columns that define the relationship need to be different.

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 :

EmpSalary = NATURALLEFTOUTERJOIN(Empl, Salary)

 

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 :

EmpSalary =
VAR A = SELECTCOLUMNS(
    Empl,
    "Empl ID", Empl[EmpId],
    "Empl Name", Empl[Name]
    )

VAR B = SELECTCOLUMNS(
    Salary,
    "Empl ID", Salary[EmpId],
    "Salary", Salary[Salary]
    )

RETURN NATURALLEFTOUTERJOIN(A,B)

 

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

1Axel
2Alex
3Mike
4Jordy

 

EmpId Salary

1330000
2500000

 

 

Thanks,

 

 

 

 

 

 

 

Anonymous
Not applicable

Hello

 

Did you get any solution for the issue you are facing ?

Did you go with the Merge query option ??

 

PY

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.