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

Hi,

Without having access to the file, it is difficult to even understand your question, let alone solve it.

Answer 2

Wow, thanks for the thorough reply. Gonna spend the day doing some reading up I guess!

Answer 3

Yeah, totally understand the limitations of not having the actual file. I'm considering making it available but because it contains sales data with customer names it needs a bit of redacting before I can do so

Answer 4

Thanks for your reply :)

Gonna do some reading up to see if I can rectify and may post again and make a (redacted) original file available. Fingers crossed I can solve it myself!

Answer 5

Yes, sharing a file helps us. This link includes a macro that can randomize text

.

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
.