Access (SQL Query) to Power BI Join

[mdalton]

Given tables A, B, C, D, and E where the column names (variables) are numeric (1,2,3,...), I have the following in Access:

SELECT A.1, B.2, A.3, A.4, C.5, A.6, D.7, A.8, E.9,..., A.30

FROM (((A LEFT JOIN B ON A.1=B.1) LEFT JOIN C ON A.3=C.3) LEFT JOIN D ON A.4=D.4) LEFT JOIN E ON A.6=E.6

In PowerBI, I have tried creating a new table with

Table=

VAR A= SELECTCOLUMNS(A, "ONE", A.1,....,"THIRTY", A.30)

VAR B= SELECTCOLUMNS(B, "TWO", B.2,)

VAR C=SELECTCOLUMNS(C, "FIVE", C.5)

VAR D=SELECTCOLUMNS(D, "SEVEN", D.7)

VAR E=SELECTCOLUMNS(E, "NINE", E.9)

VAR F=NATURALLEFTOUTERJOIN(A,B)

VAR G=NATURALLEFTOUTERJOIN(F,C)

VAR H=NATURALLEFTOUTERJOIN(G,D)

VAR I=NATURALLEFTOUTERJOIN(H,E)

RETURN I

After reading some forums, this is what I came up with. I did have an evaluate but PowerBI gave me an error message. With what I have now, my error is "No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column."

How do I tell it what variables to join on? Am I using the right type of join? Do I need to manually activate/deactivate the relationships? Are there any reprecussions to doing that? Is there an overall better way?

Thank you.

[v-xiaotang · Accepted solution]

Hi @mdalton 

Thanks for reaching out to us.

please check your data according to this tutorial, NATURALLEFTOUTERJOIN function (DAX) - DAX | Microsoft Learn

if this doesn't help, please share your sample file, then we will check it.

Also, you can try Merge function in Power Query, Power BI: Merge and Append Queries - Perficient Blogs

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[mdalton]

I ended up doing a merge... I don't know why I did not do that in the first place. Thank you.

[mdalton]

I ended up doing a merge... I don't know why I did not do that in the first place. Thank you.

[mdalton]

I know it is not related to the relationships. BECAUSE I went ahead and built summarize tables only containing the information I need and the variables that connect the tables. I went into manage relationships, connected them all with no trouble, adjusted my above DAX to the summarize tables I just built... Still got the same error "No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column."