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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Carl1985
Helper I
Helper I

Which Join is the right one?

Hello to everyone, 

 

i have a little problem. I'm not quit sure how to solve it. I have to create a table with production ordres since 2018 up to today. 

Because it is just possible for us to write querys / reporst in ERP System which contanis not more than 215 charaters (not colums!!!) I have to write three different reports to get all the informations. So far so good. 

 

All reports contains the same number of rows, in ervery report I wrote the same three fields in front of the report, which im using for connecting the tables in power query. This are the fields production order number, postion on the workorder and the number of the workplace as you can see in the following srceenshots. For every of the three reports the rows for the prodution order 100005 are twelve rows as result. 

Carl1985_0-1659018838230.png

 

Carl1985_1-1659018881490.png

 

Carl1985_2-1659018902142.png

When I'm going to combine these three reporst by using these three fields as connector i would like to have twelve lines in total for the joined table of all colums. 

I tried first left outer join and second inner join as joins. The problem is that I get about 84 rows for the joinded table. It's seems a little bit like using a crossjoin for the tables. 

 

Could you help me to find a solution for this joining problem? Do I have to use an other join type, or is it just not possible what I expected and would like to have as a result?

 

Carl1985_3-1659019182611.png

Because in total every report contains 144.008 rows and these leads to more than 1'048'576 rows for the joined tables. 

 

Thanks in advance. 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hello @Carl1985 ,
the problem with the crossjoin-like behaviour is that your join fields don't create unique rows. So for each row in your table(s) there are multiple rows in the other tables (and in the same table itself). So with each join you are expontentially increasing rows.
Is it possible to create a unique row-identifier from the existing fields and join on them instead?
If not, the only option would be to match on the existing order of the row in the 3 tables itself. To use that you would have to add an index column to all your tables and join on them instead.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
Carl1985
Helper I
Helper I

Hey Imke, thanks for your fast reply. This morning I first had my own solution: I created the first report with aggregate values. So the quantity of rows differ from the other two reports, and then connetig the reports among themselves. At first this seemd to be a good solution, but after looking more closely to the data I had to recognize that there a still some wrong qountities of rows for some values. Now I took your proposed solution, and it works fine. thumbs up

ImkeF
Super User
Super User

Hello @Carl1985 ,
the problem with the crossjoin-like behaviour is that your join fields don't create unique rows. So for each row in your table(s) there are multiple rows in the other tables (and in the same table itself). So with each join you are expontentially increasing rows.
Is it possible to create a unique row-identifier from the existing fields and join on them instead?
If not, the only option would be to match on the existing order of the row in the 3 tables itself. To use that you would have to add an index column to all your tables and join on them instead.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors