•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Power query append 3 workbooks and keep manual comments
Our take
Are you facing challenges with Power Query while trying to append data from three workbooks and retain manual comments? When appending a third workbook, you might encounter duplication issues, particularly if new data from the third workbook is misidentified as coming from the second. To address this, consider using a combined key of the index and workbook name. This approach can help maintain unique identifiers for each entry, ensuring that your manual comments remain intact while accurately capturing new data.
I had used the code here https://www.reddit.com/r/excel/comments/ek1e4u/table_updates_via_power_query_whilst_retaining/ for a query that pulls from two workbooks and it works fine. However, when I introduce a third workbook, it breaks. New data added to the third workbook is somehow considered from the second workbook and it just duplicates the last row of second workbook.
When I flip the append query by putting the third query first then the second, it works in capturing the new data. However, this causes the manual comments I entered to disappear.
I am using an index column as a key and that's why this is happening. How can I fix this? I am thinking I would need to key by using index+workbook?
Q1
let Source = q4 in Source Q2
let Source = Excel.CurrentWorkbook(){[Name="data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Name", type text}, {"date", type datetime}, {"description", type text}, {"category", type text}, {"amount", Int64.Type}}) in #"Changed Type" Q3
let Source = Folder.Files("C:\Documents\testing"), #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "ytd w1.xlsx" or [Name] = "ytd w2.xlsx" or [Name] = "ytd w3.xlsx")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}), #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Columns", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform File"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"date", type date}, {"description", type text}, {"category", type text}, {"amount", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Name", "date", "description", "category", "amount"}) in #"Reordered Columns" Q4
let Source = Table.Combine({q2, q3}), #"Removed Duplicates" = Table.Distinct(Source, {"Index"}) in #"Removed Duplicates" [link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Power query and manual table next to itHi, I want to pull data verbatim from a spreadsheet my team uses and use data from it for my own purposes. The main goal for using power query is that the data updates on my spreadsheet. Mainly, if any new entries are added at the bottom. I also have some manual fields that I need to add that correspond with the power query data. I've added another table beside the power query data, and filtering it causes the data on both sides to adjust correctly. I'm mainly concerned that, if the entries are rearranged or sorted on the original sheet, that my tables will not align after a refresh. Also, if a refresh would break my table alignments at any point. Is my fear founded? Is there a way to combine the two features that I need into a single table? submitted by /u/Perspective-Guilty [link] [comments]
- Power Query Merge Issue - Duplicate Tables After Refresh?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! submitted by /u/Resident_Quantity827 [link] [comments]