Tools limited. How to automate multiple SQL server queries -> Excel workflow at work?
Our take
Hi everyone,
The initial process was to use a macros enabled excel template for data cleaning and reconciliation (we can still use macros but just this process alone takes a long time to get thru thousands of accounts cos each account needs to be reconciled).
I would, -> run a couple of different queries in sql server -> copy & paste results into the excel template -> clean and reconcile debit/credit -> color code and mark tabs to be sent to manager for approval along with a sox template.
I need this entire process automated somehow. My permissions are limited so at this point I can only work with sql, excel & power query based on my research (I don’t have prior experience with power query)
Has anyone here done something similar before cos I could use some advice. I am trying to see how to integrate the many queries into this as well as what the end product should look like. I just want to create a more efficient process so that I can show my managers and perhaps they can incorporate it in a bigger scale if applicable. Thanks in advance!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power query automation with combining two tabsI want to create either a skeleton workbook folder or a template where people can upload their data and it runs all of the conditions on power query that I want. Also, it pulls definitions from a secondary tab and matches them with terms that are from the query and merge them. I basically just want them to be able to paste their data raw and it comes out the way I format it with the steps I’ve already created in query. I have watched every YouTube. Searched. Everything We write a report every month and I am trying to make it a very user-friendly report for them and minimize the extra information They don’t need and also link definitions to be able to understand. Please help. submitted by /u/HeavyBreadfruit3667 [link] [comments]
- Any point in using a no-code ETL tool to transform excel?I'm trying to understand whether I should be using a third party tool to do some data transformation to join/filter a few spreadsheets on a regular basis or simply try and learn Power Query (which I don't like to tbh, but maybe that's because I haven't used it enough). What do you guys think? Should I go all in Power Query for a while, or maybe explore some other tool (no code, preferably) to create a "workflow" and run them from time to time? Have you guys experienced anything similar to this? submitted by /u/Remote-Ad-6629 [link] [comments]
- I’m researching how people automate repetitive Excel/CSV workflows in day-to-day work.I’m building a desktop tool to set up these processes visually, but before going further I want to understand what actually hurts today. I’m not selling anything in this post, just trying to learn from real-world cases. If you work with this kind of thing, I’d really appreciate your input: - What was the last annoying Excel/CSV workflow you automated? - How often does it run? - What was the most frustrating part: combining files, cleaning data, standardizing columns, deduplicating, exporting, etc.? - Are you the only one who runs it, or do other people need to run it too? - What do you use today: Power Query, VBA, Python, Power BI, something else? Thanks! submitted by /u/Remote-Ad-6629 [link] [comments]
- Any faster way to merge large Excel reports automatically?I am working with financial and operational data in Excel and facing a recurring issue. Every day multiple reports are generated separately and each file contains thousands of rows of data. The challenge is that I have to manually combine all these reports into a single dataset before doing any analysis or building dashboards. Even using Power Query and sorting takes a significant amount of time when the files are large. Is there a more efficient approach to automate this process. Ideally something that can automatically pull multiple files and merge them into one structured dataset. Has anyone dealt with something similar? Would appreciate any suggestions or tools that could make this faster. submitted by /u/WoodpeckerNo5214 [link] [comments]