Left Outer Join Issue

[Matas]

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! 

[speedramps]

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 !

[Matas]

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

[speedramps]

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 !

[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

[speedramps]

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.

[speedramps]

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.

See https://community.powerbi.com/t5/Power-Query/Merging-tables-with-Left-Join-returns-rows-with-no-matc...

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 type

Sort by Bus_OBJKEY
Remove blank Bus_OBJKEY

Keep top 1000 rows

Edit table 1Head"

Remove all steps except Source
Remove all columns except  Bus_OBJKEY and Document Id

Clean 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 ☹️☹️☹️

[speedramps]

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 !

[Matas]

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.