Log In
Register
Home
Active Member
January 7, 2021
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Frantisek,
Without a sample file, it's hard to help you. Can you upload one?
Active Member
January 7, 2021
Offline
Thank you, Mr. Bombea
File added to attachments, this time, hopefully, successfully.
(No of componenty.xlsx)
January 12, 2021
Offline
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/.....779688874d
Please let me know if that works 🙂
Active Member
January 7, 2021
Offline
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.
1 Guest(s)
