Power Query: Merging Columns based on Article ID

[Silverborn]

Good afternoon,

I'm currently trying to merge two columns that have similar Article ID's, but one list has 17.000+ positions, the other only has 13.000+ positions. All 13.000 match the ID from the first list. Basically I want to add the new columns from a different database and match them with the positions in my first list. The 4000+ missing matches should simply be skipped. So it looks a bit like this:

1 - 1
2 - null
3 - 3
4 - 4
5 - null
6 - 6

Right I can merge them and my Main database still has 17.000 positions. When I try and merge them the new query matched the 13.000 perfectly, but the query now removed the missing matches from my new list. My new list now looks like:

1 - 1
3 - 3
4 - 4
6 - 6

And I would like to see all of them. Just adding the extra information where possible. By using the regular option I see my "ImportTBE_Extra" listed as a table. If I scroll back to the left my ID's are still intact, but once I expand the "extra" it filters out ID's from my original list.

The original 3 are in the right order:

When I expand the "extra" list, I can see I'm missing a couple already:

I'm missing something and most tutorials are about exactly matching lists. How do I solve this? I can do it manually with x.lookup, but I'd rather do this with Power Query to keep my import files clean of formula's.

Thanks in advance!

 

[RoryA]

I can't replicate that behaviour. Are you sure they are actually missing, rather than just the data set having been put in a different order?

 

[Silverborn]

I can't replicate that behaviour. Are you sure they are actually missing, rather than just the data set having been put in a different order?

I forced it, see what happens. Asked my boss what the reason was I'm exporting 4000+ less items than him. He said that he could see hidden articles that have been removed from sight all together. Which means, it doesn't really matter that they are missing. So technically I can use the shortened list. But for the sake of consistency (and importing files later) I do wanna try out what I had in mind.

I found out the original list has stayed intact, and the new items have connected themselves on the right positions. Why Power Query isn't showing me this in the resulted list correctly, I have no idea. If I replicate the situation, same screens can be shown. It's mostly very confusing why my result screen (PQ example) is missing the data it does push out in the final file.

 

[Silverborn]

Might have been useful information as well:
I did check the article lists with a 'search for doubles'. The lists truly are 17000 vs 13000. And the 13000 lists 100% matched with everything in the 17.000 list.

It looks like this now. Which is what I wanted, although a cleanup might still be in order.