•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
I need a way to combine multiple workbooks to use in a pivot table that can be refreshed through sharepoint.
Our take
Combining multiple workbooks for a pivot table can streamline data analysis, especially when using SharePoint. You’ve made great progress by leveraging Power Query to merge your monthly tables, but encountering refresh issues can be frustrating. To ensure consistent functionality, consider centralizing your data in a single workbook that dynamically links to your SharePoint files. This approach can reduce complexity and improve refresh reliability. Additionally, check your connection settings and permissions to avoid errors related to legacy connections.
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).
[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]
- PowerQuery and add manual dataHi everyone, I have a Power Query in Excel that outputs a table with [title] [date]. I need to manually add the Sprint number in an extra column [sprint] to specific combinations, aka "I will work on this this month." The problem is every time the query refreshes, any manually entered data gets lost or misaligned. - New rows come with the needed values. - Row order changes. Because this table is used by many people, I want them only to add the sprint number, nothing else, no copying data or anything. I would like to know more about your experiences when data needs to be written infrequently but many times. I am open to know more for powerbi options direct dashboard too. submitted by /u/No_Solid2349 [link] [comments]
- How do you handle version control when multiple people touch the same Excel file?My team has a shared Excel file on SharePoint that three of us need to update throughout the week. Nothing crazy, just sales forecasts and pipeline data. The problem is we keep overwriting each other's changes accidentally. One person opens it, forgets to close, someone else saves over their work. We tried naming conventions like v2 and v3 but that got messy fast. I know co-authoring exists but sometimes people just don't refresh or they open the desktop version while someone is in the browser. What systems or workflows actually work for keeping things straight without a dedicated data person? Curious if others have found a simple method that doesn't require everyone becoming an Excel expert. submitted by /u/Southwesterhunter [link] [comments]
- Creating Pivot Table from Multiple SheetsHi All, I'm working on a large tracking workbook, consisting of several clinical trials in order to track by patient detail the payments we are owed by the funder, what we have received, and the difference. All these payments are delayed by 3m-2 years in some cases and leadership wants to accurately predict how much we are owed. I think what where I'm running into issues is that while I did standardized as much as I could, there are still several columns for each study that don't apply to other studies. I.e. some studies have different arms they could be enrolled in, some are just a 1 time enrollment payment, others have several milestones that can receive payments. But every sheet has roll ups that are standardized that I need in the Pivot Table. Those being: Protocol Randomized Date Federal Accrued Foundation Accrued Industry Accrued Supplement Accrued Federal Received Foundation Received Industry Received Supplement Received Total Owed The Accrued and received columns sum the individual payments into those buckets, that way we can go back to the funder and ask specifically what we are missing for to see if they missed paying us for that milestone specifically. When I tried pulling all these sheets into Power Query, I was able too, and aggregated all the sheets into one via Power Query. Then I tried to pull that aggregate into a pivot table. No Pivot table Loaded and all I got was "load to data model failed" on each queries. Am I asking for too much? Can I get rid of the extra columns in Power Query that do no align together with ruining the data that is being pulled in by formulas. I have if statements pulling into the table for the individual, study specific milestones, from a separate table that automatically helps us track payments accrued, and the "standard columns" have sums formulas that sum the columns that apply to them from the individual milestone columns. The milestone, study specific received columns are entered in manually and have no formulas, but are rolled up into the standard columns just like in the accrued side. And the total owed column is also a formula of the standard accrued and received columns. The goal of pulling this into a pivot table is so we can give high level data to leadership to actually start tracking how much we are owed, given the constant delay in payments, and to have a real sense of the deficit this specific program runs year to year. This way they can accurately plan for the yearly "donation" from other sources of funding in the department. If you made it through this post, thank you! Any help is appreciated. I'm using Excel 365. submitted by /u/Melodic-Pollution-91 [link] [comments]