Forum

Power Querry keeps ...
 
Notifications
Clear all

Power Querry keeps duplicating when placed and adding rows after refreshing

5 Posts
3 Users
0 Reactions
299 Views
(@hawkins)
Posts: 3
Active Member
Topic starter
 

Hello!

I'm a total beginner with anything advanced in Excel (I started with the Power Query just today). I followed a few youtube tutorials to get the basics, too.

I used PQ to merge several source Tables, each in a separate sheet, into one, that is to be Consolidated into small Table that shows how many of which component to order, meaning merging duplicate Component name cells into one, and adding their respective number of Pieces together to form a final number. It should also ignore and remove empty cells. SJZ, Type and Manufacturer columns are not important.
Source Tables are supposed to be easily replaced just by copying-in from a different excel file. Should work just fine as long as Table name is preserved.

This works on each separate sheet (for control, using Power Pivot), but I can't get it working on the whole thing.

File attached.

Can somebody help me, please? The PQ not working properly is my biggest issue now.
Thank you.

Also, not native english speaker, so, sorry for grammar.

 
Posted : 08/01/2021 9:50 am
(@catalinb)
Posts: 1937
Member Admin
 

Hi Frantisek,

Without a sample file, it's hard to help you. Can you upload one?

 
Posted : 09/01/2021 11:56 am
(@hawkins)
Posts: 3
Active Member
Topic starter
 

Thank you, Mr. Bombea

File added to attachments, this time, hopefully, successfully.

(No of componenty.xlsx)

 
Posted : 12/01/2021 1:36 am
(@thenewbee)
Posts: 26
Eminent Member
 

Hi,

you can select in your power query all colums, made an right klick, and select "remove dublikates".

 

THis is unfortunately in German:

https://support.microsoft.com/de-de/office/entfernen-von-duplikaten-power-query-d9cffc69-dc5d-4d94-8b66-72779688874d

 

Please let me know if that works 🙂

 
Posted : 13/01/2021 8:59 am
(@hawkins)
Posts: 3
Active Member
Topic starter
 

Hello!

Thank you for your help. It works better now, although I encountered a few situations where it does not work properly.

When I make edits to some of the source (blue) tables, it adds them to the final (green) table, this is fine, but if I remove some components from the source tables, and refresh the whole file (ctrl+alt+f5), the components stay in the green table, despite them being not present in the source tables. (can be seen with the Random Thing, and Wrong Thing component)

Also, if I add the exact same component (I named it That Thing) with the exact same number of pieces to each source table, the final green table shows there is only one component, when it should shouw there is five of them. This could be caused by the 'remove duplicates' function?

 

Think of this whole thing I'm trying to achieve is basically a shopping list, that merges several smaller lists into one large.

 
Posted : 14/01/2021 8:17 am
Share: