Problem with Merge with Power Query

0

Dear all

I have a question on merging 3 tables together with Left Outer 

As this is one to many relationships, the row repeat if there are more than one record under the Primary Key.

I have attached the file for your reference

Appreciate your help

Eric

Answer
Discuss

Answers

0

Thank you for this question. It gave me an opportunity to learn about Power Query. This was my first serious contact with the program. Therefore please don't value this response as an answer and bear with me for my ignorance.

I figured that the result you want is very similar (except for only one partial row) to a filter applied to the X_GestName column to remove all null values. Therefore I concentrated on a method how to integrate that filter in the query itself. As are most things about PQ's UI, it's surprisingly simple.

In the Power Query editor you have filter arrows in the caption row. Click on the one in the X_GuestName column and remove the checkmark against null.

As I said, this still leaves L34:O34 highlighted by you as undesirable in the result. I see no difference as compared to L33:O33 and therefore wonder whether I understand your question correctly. Please do get back here to explain what you need. And thank you again for giving me this opportunity to sharpen my wits on a real life problem. If I'm really lucky my effort was of help to you, too. I hope so.

Discuss

Discussion

Dear Variatus

Thanks so much for your reply, I am really appreciated, as I have been checking with many forums but not feedback.

As there are 3 tables, I selected the column to display 

A-E -belongs to the 1st table
F-K - belongs to the 2nd table
L-0 - belongs to the 3rd table.

If you take a look on the original tables and expected results
You may find that A31:F32 records are duplicated and should not be there.
As I just LeftOuter Join, somehow, Excel produces these.

In other words, whenever I have more rows with same code, it "reproduces"record.. 

Eric  
EricTsang Mar 22, '20 at 7:06 am
Sorry, Eric, this is above my pay grade.
Variatus (rep: 3563) Mar 23, '20 at 12:40 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login