Power Query to tackle consolidating large database to reports or other approach?
Our take
I currently manage getting some financial reports out to multiple manufacturing facilities from a large corporate snowflake database. I've struggled with multiple challenges in our current process and I'm rethinking this from the beginning, I have an idea but before spending tons of time trying to implement it and possibly hitting a brick wall I wanted to throw it out here to see if you can save me some time/pain.
So currently I have data connections from snowflake to a big Master Excel file that selects the fiscal year and period and imports large amounts of data. This data is then cross references with other tables in the Master Excel file to group the transactions to certain categories and business units the plants want to see. This grouping data does not exist in the snowflake database. We then filter this data to each of the specific plants and copy/paste (which has a couple issues/problems I just cannot seem to solve) into workbooks that then present it the way they want to see it, sorted by categories and departments but still with all the individual transaction details for their plant to investigate curious issues. This is prepared and sent out weekly for the fiscal months (shows last week info and month to date info).
I had previously set up an access database that I could then link to from excel to do something similar so I was considering building the links and filters into each plant and using power query to access/import the categorized/filtered data into each plants file eliminating the middle step and any copy/pasting. This is option 1.
Option 2 and my real question, can I set a similar query up in an excel file to link to the snowflake databases, with the categorizing tables and such in it and then use power query to import from this Excel query master file to do something without using Access? Benefit being maintenance by other people in the org that are not familiar with access. My big concern being the snowflake databases are huge and without the current year/period filter would easily surpass the number of rows excel will handle. The month/period and plant filters would be in the individual plant files importing only current/relevant info, but the Master Excel Query would be open, set to import the whole history, but that file would never be opened/used except to possibly update a manual lookup table for a new account or business unit. Would this work over the access approach?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Excel Query vs data connection, Query much slowerI've been working on overhauling some reporting and really struggling to get a good/fast/easy to maintain setup. Currently I have a workbook with a fixed "Connection" (under queries & connections) to snowflake databases that can import about 66K rows of data in roughly 10 seconds. Cannot use the connection in power query or data model as I need so I tried recreating it as a query, copy/pasted the connection string and command text from the connection into a Query so that I can more freely use it, but it goes from 10 seconds to refresh to 3-4 minutes to load the exact same 66K rows of data. Is there something I'm missing? submitted by /u/robbro9 [link] [comments]
- Power query for a large datasetMy company uses a horrible format for its daily production sheets, but the data can be pulled through power query. I want to build a reporting tool for looking at any major trends that are currently missed. Ideally looking at part efficiency by machine type and some other descriptive data too like efficiency by shift manger etc. My problem is that even after cutting unnecessary columns and filtering unnecessary rows, it takes forever to load anything. ChatGPT isn’t all that helpful, I’d like some expert advice please! For info, rough number of rows of data is about 50,000 per year. I want to cover at least the last three years. Sheets are all saved into a folder by month, within a folder by year. submitted by /u/CanJesusSwimOnLand [link] [comments]
- In series drop down filters from multiple sheetsPortion rant and question. Unfortunately, being able to create pivot tables has earned me the title of "Excel Wizard" in my office, and I have been tasked with creating a dashboard to pull filtered data from several sheets easily. The database I am pulling from is fairly large, and outside of my abilities, YouTube and online searches are not getting me the exact answer I need. In theory, the end result will be a dashboard with two dropdown filters. The first is to select the specific location (37 total), and the second is to select information from 10 separate sheets, like contact information, contract expirations, insurance policy information, equipment information, etc. They would also like each category to have its own sheet so the information can be looked at as a whole. I have pushed to have seperate excel files for each location with the information needed, but they want one place to view and edit all of the data. The other caveat to this is that since pivot tables are a mind-boggling creation, I fear any complex formulas or functions may get damaged as they try to edit/update information in the data sheets. My initial thought was to consolidate all of the information onto one sheet, but the different headers/information types stopped that plan quickly. Besides advocating more for some type of software to store this information and accomplish this "dashboard" need, is there a solution to my problem? submitted by /u/Few-Combination-9985 [link] [comments]
- Summarize simple use of Power Query for impatient experienced user with simple need?I'm a longtime Excel user, comfortable in using it for all kinds of text manipulation and basic number crunching, beginning to get a handle on tables and new 365 formulas. I've learned most of what I know from self study and enjoy it, but at the moment I'm a bit overwhelmed with trying to get started with Power Query. It seems like just the tool for what I need to do, but it's also designed for a lot more, and I'm having trouble getting my bearings since even the basic introductions assume that my needs are much more complex than they are and talk about a lot of options that are irrelevant to me. This is all I need to do: I have a workbook with a table where I keep track of work on inquiries sent to an online platform from which I can export submissions into Excel. The export contains a few columns I don't need, and lacks a few others that I add in the workbook, so what I've been doing is opening the export file, deleting unneeded columns, adding blank rows to the bottom of the table, copying the data from the export file, and pasting it in beginning with column D. Seems like just the kind of thing to automate with Power Query. But I don't want to keep the export file anywhere once I've appended its contents to the workbook. The data remains in the online platform if I need it, and storing any subsets would only waste space and cause confusion. I want to export the latest submissions, transform them with Power Query (I've already figured out how to do that), append them to my workbook, disconnect the two files, and delete the export. Sounds simple enough. But I can't seem to find out how to do this very simple thing without having to sit through long explanations of things I don't need to do and am too busy to wrap my head around right now. Can anybody point me to some simple step-by-step directions that just tell me how to do only what I need to do without bothering with all the other possible scenarios? Or is Power Query overkill for my needs? Is there another alternative? Thanks. submitted by /u/fastauntie [link] [comments]