•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Power Query Merge Issue - Duplicate Tables After Refresh?
Our take
In this discussion, we'll address a common challenge faced when using Power Query to merge tables from two Excel sheets. After merging, users often find that the original tables are duplicated upon refresh, leading to confusion about managing updates effectively. Key questions arise: Will the merged query automatically reflect changes made to the original sheets? Is it typical for Power Query to create duplicate tables during this process?
Hi everyone,
I have two Excel sheets, each containing a table. I’m using Power Query to merge them into a new table (Merge query), and everything works fine initially.
However, after I load the result back into Excel, I notice something odd:
The merged sheet (called "Merge") is created correctly.
But the original two sheets seem to get duplicated again as separate tables (like "Table1" and "Table2").
My main concern is this:
I regularly update the data in the original two sheets.
So my questions are:
Will the merged query automatically reflect updates from the original sheets when I refresh and where to but the new data in the orginal sheets or the new table 1 and table 2?
Or do I need to set up something specific to make sure the merge stays updated?
Also, is it normal for Power Query to create duplicate tables when loading, or am I doing something wrong?
Any clarification would really help. Thanks!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power Query Merge causing missing values (~7k difference in totals)I’m merging two large tables in Power Query, but I’m running into a data accuracy issue. After the merge, I noticed that the sum of a specific column is about 7,000 less than the total in the original table. So it looks like some data is missing during the merge. I’m working with large datasets, and I want to properly debug this. What are the most common reasons for missing values after a merge? Could this be join (e.g., Inner vs Left)? How can I identify exactly which rows are missing? What’s the best way to troubleshoot this and make sure no data is lost in the merge? Any help or tips would be appreciated 🙏 submitted by /u/Resident_Quantity827 [link] [comments]
- Issue with Excel Power QueryIn my excel workbook I have a long string of queries to get the results I want, however I am noticing a small number of duplicates that SHOULDNT be able to exist. In my first query in this string, I am adding a new column (SelectionBucket), based on two other columns - Works. Then taking this SelectionBucket column, and adding another column (IsRequiredBucket) based on [SelectionBucket] returning one of the required values - Works. I then am adding an index at this time (CourseIndex) - Works. Result: Courses have Index, and SelectionBucket and IsRequiredBucket as options. Q2 (Reference to Q1): Adding Column (IsRequiredCandidate) where [IsRequiredBucket] = True - Works. Filters out to ONLY true values next, and sorted on (Name) (Ascending), (SelectionBucket) (Ascending), (EMark) (Decending) - Works. Result: Filtering down to only RequiredBuckets, sorted by Best to Worst. Q3 (Reference Q2): I group the rows based on (Name) and (SelectionBucket), call it [AllRows]. Add Column (TopRequired) with Table.FirstN(Table.Sort([AllRows], {{"EMark",Order.Descending}}),1) to return the BEST value - Works. Expand the [TopRequired] Table, excluding Name and SelectionBucket - Works. Add column (SelectionType) = "Required" This is where I am not sure if it is working or not, because for 99% of my data, this works. But for some of the entries, this isn't working. Add one more column (IsRequiredSelected) to check (SelectionType), if "Required" = TRUE. Result should be: Selection of one result for each of the buckets available per entry, and setting its (IsRequiredSelected) value to TRUE. Q4 (Reference Q1): I merge Q4(which is just Q1), to Q3, matching on (CourseIndex), Expand the merge (SelectionType). Rename (SelectionType) to (RequiredTag). Add column (IsRequiredSelected) checking [RequiredTag] to return TRUE for "Required", FALSE otherwise. Q5 (Reference Q4): Add Column (IsAdditionalCandidate) checking [IsRequiredSelected] = FALSE. Filter (IsAdditionalCandidate) for only TRUE values. Sort by (Name) (Ascending), (EMark) (Descending). Result: Check if (IsRequiredSelected) = TRUE and clearing them out. Q6 (Reference Q5): Group by Name -> [AllRows] with operation of All Rows. Add column (TopAdditional) coded =Table.FirstN(Table.Sort([AllRows], {{"EMark",Order.Descending}}),7). Expand the table [TopAdditional] excluding (Name). Add column (SelectionType) = "Additional" Result: Taking only records that are marked as "Additional" and taking the best 7 results for each (Name). Q7 is an appended query of combining Q3 and Q6, where it should take the Q3 Results, and adds the Q6 results to it, which should result in NO duplicates. Issue: I am receiving some of my entries as a duplicate through a query check, where I see the one record as both an Additional and as a Required. I am not sure WHY or where it is broken, other than where I think it is... I realize I could have done this in less Queries, however I wanted to verify along each step of the way if something went wrong, so that I could fix it as a portion, instead of having to delete and re-write everything. Please note that I CANNOT share the excel file data itself, as it contains confidential information within it. If I haven't explained a step clearly enough, please let me know and I will try to add further information on it. submitted by /u/DLCamilla [link] [comments]
- Conditional calculation for duplicates in Power Query / Excel?I have a column called “Total” that contains the total price of a product. The issue is that sometimes the same product appears more than once (duplicate rows). When that happens, I want to apply a specific calculation so that the total is adjusted correctly for each duplicated row. But if the product only appears once, I want to keep the original total without any changes. Also, this table is created by merging two tables using Power Query, and it gets updated regularly whenever new data is added to the source tables. So basically: If there are duplicates → apply a custom calculation If not → keep the value as is The solution should still work correctly after every refresh Is there a way to handle this dynamically in Power Query or Excel? Any suggestions or approaches would really help submitted by /u/Sure-Barber-4161 [link] [comments]
- Power Queries as inputs changeI have a main spreadsheet that is fed by some instrument logs among other things. These inputs change from time to time due to instrument software updates. I can update the power Queries but then it breaks the old files. How do people handle this? submitted by /u/Javaslinger [link] [comments]
Tagged with
#Excel alternatives for data analysis#google sheets#generative AI for data analysis#Excel compatibility#Excel alternatives#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#natural language processing for spreadsheets#Power Query#Excel sheets#original sheets#merge query