Forum Discussion
Left Outer Join Issue
Hi guys,
I have an issue regards Left Outer Join, where I would like to join the table from the "Block_Log_Base" table based on "Bus_OBJKEY" to the "1Head" table based on "Bus_OBJKEY" as well, in order to expand and allocate the right Document ID to the documents in "Block_Log_Base", which does not have as default.
Just some info: Block_Log_Base table in the database contains 38,343 documents and the 1Head table contains 643,683 documents.
My approach: I am merging the "Block_Log_Base" table with the "1Head" table based on the "Bus_OBJKEY".
The selection merges all of my 38,343 Documents. That is great!
The issue appears when I am trying to Expand the column - Document ID.
After this Expansion, my table changes from 38 thousand to 300+ thousand records containing ~85% of empty or null rows.
Does anyone have an idea where this issue might be coming from? I am pretty confused, since I have built many dashboards but facing this kind of issue for the first time. Cheers!
8 Replies
You have records with duplicate "Bus_OBJKEY" keys on table "1Head".
See attached screen print.
So the join is retreiving multiple records.
It is simple to fix.
In Power Query sort table "1Head" by "Bus_OBJKEY"
Then remove "Bus_OBJKEY" duplicates.
Now so the join, and it should work as desired.
Thanks for reaching out for help.
I have helped you, now please help me by giving kudos.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
Hi,
Thanks for your answer.
I did have removed Bus_OBJKEY Duplicates from the 1Head table. So now when I am merging tables and expanding their Document ID I am getting 600k+ thousand instead of 38,843 results. So I believe that your suggestion did not solve my initial issue. Do you have any other ideas? It would be highly appreciated.
A few suggetions ....
Suggestion 1:-
I notice you had rows with null Bus_OBJKEY.
Replace Bus_OBJKEY null values with "*" or something else in the first or second table.
Suggestion 2:-
Did you sort the sort table "1Head" by "Bus_OBJKEY" before removing the duplicates?
There is a known bug if you dont do the sort first.
Suggestion 3:-
Are you using the latest version of Power Bi Deskop ?
Unistall the old version and install the latest version.
Suggestion 4:-
What datasource are using? It it some multi row per record datasource like XML or JSON ?
Try check the layout is ok in Excel or Notepad
Suggestion 5:-
Take a backup copy of your PBIX
Then test what does and does not work to pin point the problem.
In Power Query ...
Edit table Block_Log_Base.
Remove all steps except Source
Click on Bus_OBJKEY
Remove all other columns
Clean Bus_OBJKEY
Trim Bus_OBJKEY
Change Bus_OBJKEY to Text data typeSort by Bus_OBJKEY
Remove blank Bus_OBJKEYKeep top 1000 rows
Edit table 1Head"
Remove all steps except Source
Remove all columns except Bus_OBJKEY and Document IdClean Bus_OBJKEY
Trim Bus_OBJKEY
Change Bus_OBJKEY to Text data type
Sort by Bus_OBJKEY
Remove duplicate Bus_OBJKEY
Merge Block_Log_Base with 1Head as new on Bus_OBJKEY
Expand Document Id
Does this work?
If Yes then start adding the steps you removed back until it breaks ... hence pin pointing the problem.
If No then I am out of ideas . Sorry ☹️☹️☹️
Polite reminder.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
Polite reminder.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
Hi speedramps ,
Thank you for the reminder, but I do not understand why are you suggesting to "accept as solution button" if your suggestions did not solve my issue, even though I appreciate you spending your time trying to help me. If I would do that and other people would face the same issue, they would not understand why doing the same steps "solved" my issue but not theirs.
Regards,
Matas
Hi speedramps ,
Thank you for your suggestions, unfortunately, non of the suggested solution worked in my case. I will try to find alternative solutions.
Regards,
Matas
Are you using import mode?
Try this
Write down one key number that has multiple output records.
In power query editor
In table A just keep records = key
In table B just keep records = key and nulls
Run your join and check output.
This wont fix your problem but it might make it easier to spot the problem with less records.
Please click thumbs up to give kudos, I tried to help you lot. Thank you.
