•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Power query multiple workbooks and sheets
Our take
Combining multiple workbooks and sheets in Power Query can be challenging, especially for newcomers. It sounds like you're on the right track by using the Excel.Workbook function and filtering your desired sheets. However, to extract data from all selected sheets, ensure that after filtering, you expand the resulting table to include the data columns you need. Instead of just loading individual worksheets, apply transformations to consolidate the data across all selected sheets before closing and loading.
I 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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]
- I need a way to combine multiple workbooks to use in a pivot table that can be refreshed through sharepoint.I have 5 spreadsheets in a folder on sharepoint and one on a separate folder in the same team site. These track contacts made by clients. Each workbook is for a LGA (location) and have the same structure. they have a sheet for each month and a table for that month. I initially struggled to use the Sharepoint Folder to combine these so created queries in Power Query. for each workbook I created a query which combined the 12 tables and then created a query which appended them. I was able to put this in a workbook and a pivot table to summarise where we were up to. it worked really well. the permissions were set using organisational account. this lasted about a day and now I cannot refresh it. I can refresh in the power query editor but not in the spreadsheet. I have opened it in the desktop version and also the same problem. I am now getting errors about legacy connections. I then tried the Sharepoint folder which finally worked but seem I can combine the January tables for the five spreadsheets but then I would need to do the same for each month. I would love any suggestions for this to consistently work and refresh from our sharepoint site (excel online). submitted by /u/Eastern_Echidna5680 [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]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#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#google sheets#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#rows.com