•3 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How can I automatically update Power Queries with new tables/sheets as they are added to a workbook?
Our take
Are you frustrated with Power Queries that don’t automatically update when you add new tables or sheets? You're not alone. Transitioning from legacy processes to Power Query can be transformative, yet many encounter obstacles. To ensure your Power Queries dynamically include new data, start by verifying that all sheets are formatted as tables and share consistent headers. This introduction will guide you through essential steps to troubleshoot and optimize your setup, unlocking the potential of seamless data integration. Ready to elevate your reporting?
Hi Everyone,
I've finished the project for my boss and now working on my personal project y'all have helped me on using VBA. Trying to change it to Power Query and Pivot Tables now that I learned more about them.
Here's my situation, I want the power queries to automatically update when new sheets/tables are added to the workbook. I did a Google search to ask for help it doesn't seem to be working.
The below is the result from Google:
- Format Data: Ensure all sheets have identical column headers and format data ranges as Tables (
Insert>Table). - Get Data: Go to
Data>Get Data>From File>From Workbook(orFrom Table/Rangefor a single workbook). - Combine: In Power Query, filter the sheets to include only the ones needed. Expand the data.
- Unpivot: Select the columns that should not change (e.g., ID, Name). Right-click and choose "Unpivot Other Columns". This ensures new columns added later are automatically unpivoted.
- Load: Click
Close & Load.
The steps I've done:
- All data on the sheets are formatted as tables with unique names
- Loaded the workbook (current)
- Filtered 'Name' to begin with XXX_ (Tables are named XXX_Mon where XXX is a company identified and Mon is the 3 letter month) -- This step results in the two tables I'm expecting
- Removed 'Item', 'Kind', and 'Hidden' -- This leaves me with 'Name' and 'Daa'
- Expanded the Data column
- A dialog pops up asking what I want, so I select Agent, Score 1, Score 2, Score 3, Score 4
- After clicking OK, I now have 'Name' & the above columns prefixed with Data.
- I selected 'Name' and 'Data.Agent' and right-clicked and picked Unpivoted Other Columns
- Renamed 'Attribute' to 'Eval'
- Renamed 'Value' to 'Score'
- Final Result: Name, Data.Agent, Eval, Score
- Click on Close & Load To...
- Selected Only Create Connection
- Click on Add this data to the Data Model
This is showing all the records (50) as expected. When I add a new sheet named XXX Mar with the table name of XXX_Mar. I refresh all, but March isn't loading in to the Power Query (still showing 50 records).
What am I doing wrong to have the query auto-add the new table?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power query multiple workbooks and sheetsI am trying to combine multiple workbooks that have multiple sheets but I am missing a step. Get data, from file, from folder and open. Transform. Add custom column (=Excel.Workbook([Content]). I select data, item and kind from the custom drop down. Then I filter item to select the specific sheets I want. Close and load. When I do this, I only get the filepaths of the sheets I selected. I tried the same steps, but I clicked on the "Table" link in the custom data column before close and load. It brings up the data for that worksheet and it's correct. But when I then close and load, it will bring up the actual data, but only for that worksheet that I looked at. How do I get the *data* from all the worksheets that I selected in custom data to load. Or am I completely off base? Thanks! This is my first foray into power query and it's a bit daunting. submitted by /u/Choice_Intention_778 [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]
- I want to use Power Query to import data received from a client, where the file name changes each month. What's the easiest way to automate this?I used to use VBA for this, but that's a lot more roundabout, and I have a lot less control over the transformation. I have no issues with transforming the actual data itself. My issue lies in the fact that it's a different file each month. Using wildcard formatting, *filehere*.xls* would always pull the correct file. This file is also stored in the same place relative to my spreadsheet each time, but the location of the spreadsheet and folders itself changes each month. In VBA, I could find the relative position quite easily via ThisWorkbook.Path & "\Data\" However, I don't know how to use PQ to import automatically like this, so that I'd always import the correct data simply by refreshing links. I think I've seen people set up a somewhat hacky way, where PQ first reads a table in the workbook to retrieve values, and then uses those to find the file to query. Is that the only way? submitted by /u/space_reserved [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]