Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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.
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.
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.
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."
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
157 | |
121 | |
73 | |
73 | |
63 |