Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
mdalton
Helper I
Helper I

Access (SQL Query) to Power BI Join

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.

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @mdalton 

Thanks for reaching out to us.

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

vxiaotang_0-1665477984736.png

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.

View solution in original post

4 REPLIES 4
v-xiaotang
Community Support
Community Support

Hi @mdalton 

Thanks for reaching out to us.

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

vxiaotang_0-1665477984736.png

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.

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

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

mdalton
Helper I
Helper I

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."

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.