Power Query: Merging Columns based on Article ID

Silverborn

New Member
Joined
Sep 5, 2022
Messages
5
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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.

1663152995782.png


The original 3 are in the right order:
1663153131347-png.73891


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


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!
 

Attachments

  • 1663153131347.png
    1663153131347.png
    111.4 KB · Views: 68

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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?
 
Upvote 0
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.
 
Upvote 0
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.

1663157216852.png


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

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top