Power query - merging query creates duplicate rows

Question

I'm trying to use Power Query for the first time and whilst I started off quite well I have now hit an issue and am stuck

So far I have managed to create links to 2 different folders where data is obtained from. The first data set is a ledger of items shipped and associated sales order (NE_SORxxxxx) and the second contains ship destinations and customer name. I have merged these and am working with the ledger containing shipped items, see pic. I have hidden ship destination and customer name, the query is named 'SHIPMENT MERGE'. UP TO HERE ALL SEEMS OK

Now I need to remove the items for all NE_SORs which have not come through our configuration center. In order to identify what NE_SORs have come through config and what hasn't I can create a new query 'From Table/Range' and filter the 'Hierarchy Level 3' column on Configuration. 'Close & Load To' -> 'Connection Only'. This gives me a list of all NE_SORs which have come through configuration

Now create a new query where I merge the list with config NE_SORs with the 'SHIPMENT MERGE' ledger, 'VLOOKUP' (not an actual vlookup of course) on the NE_SORs and filter out any 'Nil'. Close & Load.

Now the issue is that this doesnt actually reduce the number of rows/lines as it should - it actually duplicates a lot of rows and the total number of rows jumps to 200.000+. I think the query references itself somewhere but I have no idea how to investigate or fix

Any guidance would be appreciated hugely, apologies if anything isnt clear - happy to clarify anything if needed

Answer 1

Here is how I identify the config NE_SORs

Answer 2

Merging the queries

Answer 3

DUPLICATES!!!

Answer 4

Unlike the VLOOKUP in Excel which will return the first matching row, the Merge Query returns all rows for every match.

It's hard to say what exactly is off here so here's a little ref https://stackoverflow.com/questions/60838153/merge-two-queries-without-duplicating-rows-in-power-query

Answer 5

It could be either the data or the type of join you use for the merge. Or you could just use PQ to remove duplicates ... if they really are duplicates.

.

It would help if you provided us with small sample data sets that recreate the problem. We can show some possible solutions.

.

How to Merge Duplicates in Excel (6 Ways)

https://www.exceldemy.com/merge-duplicates-in-excel/

Tanjima HossainMay 19, 2022

If you are looking for some of the easiest ways to merge duplicates in Excel, then you are in the right place. It can be helpful for a dataset containing a large number of duplicates to merge them and make the dataset clean and tidy. So, let’s get into the main article.

.

Keep or remove duplicate rows (Power Query)

https://support.microsoft.com/en-us/office/keep-or-remove-duplicate-rows-power-query-d9cffc69-dc5d-4d94-8b66-72779688874d

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010

When shaping data, a common task is to keep or remove duplicate rows. The results are based on which columns you select as the comparison to determine duplicate values is based on the data selected.

.

Bonus Reading

Here is some general info about Join types

Multiple JOINS Types for Beginners | Power Query and Power BI Tutorial- 4min YouTubehttps://www.youtube.com/watch?v=JF_x0qTd5vA  4min
In this video, we will learn about how to use different types of Join in Power Query and Power BI.
.

Power Query Join Types 20min Youtube- Nov 28, 2020https://www.youtube.com/watch?v=8CMWDb-Mrow
Power Query Join types deep dive. Learn about how to create the following:
Left outer join
Right outer join
Inner join
Left anti join
Right anti join
Full outer join
.

Seven Types of Table Joins https://www.powerbi-pro.com/en/power-bi-seven-types-of-table-joins/
This article “Seven types of table joins” was written in preparation for a user training and I don’t want to keep it from you. At the very end, I will also show two ways of circumventing case sensitivity.
.

Upload Example - Share via “Personal” OneDrive File

There is no way to upload example files directly to this forum.
.

Trouble shooting problems using this text only forum can be like a visit to the dentist without anaesthetics: a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we hopefully can eventually come up with a / “the” solution.
.

Often it is faster and easier for everyone if we have a “sample file(s)” get "hands on", to look at, and to “play with”. There are a couple of advantages of providing example files:

. * we have exactly the same data you are describing,

. * if your problem requires merging multiple files, we can work with that

. * you will often get different suggestions from users with points of view and experience.
.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:
https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156
.

The above article includes links to a macros to randomize existing text in Word and Excel
.

Note: make sure to upload to your Personal (consumer) OneDrive rather than your work OneDrive or SharePoint. Files in the Work cloud can be hard to share due to security restrictions placed by the work admins. Or use any other free storage service (anything other than business OneDrive or SharePoint).
.

Another article explaining need for sharing example files:

Why a sample file is important for troubleshooting. How to do it. https://answers.microsoft.com/en-us/msoffice/forum/all/why-a-sample-file-is-important-for-troubleshooting/9441ae3c-1e92-41c6-9a1f-5b377b08e5a5
.

2022 05 06- The 9 best cloud storage services: Cost, free storage, and features compared
https://www.zdnet.com/article/best-cloud-storage/
What is the best cloud storage service? There's no one size fits all.  To help you decide which one is best for you, let's look at the top cloud storage options.
.

2021 05 25- 15 Best Free Cloud Storage in 2021 – Up to 200 GB Free Storage
https://www.whizlabs.com/blog/best-free-cloud-storage/
.