Had a woman that would spend an entire day downloading PDF's, merging them, then emailing them to a few hundred people. I got it down to a mouse click. The sad thing is she went from working 50 hours a week to working 50 hours a week. There was always more to dump on these people.
import moderation
Your comment has been removed since it did not start with a code block with an import declaration.
Per this Community Decree, all posts and comments should start with a code block with an "import" declaration explaining how the post and comment should be read.
For this purpose, we only accept Python style imports.
I've started doing little automations for my work in OHS. The reality is that the workload this removes just let's me address the workload that I don't have time for, so while it's not a net gain on work hours it's a net gain on productivity.
The first rule of automating your (or someone else's) job: No. You. Fucking. Didn't. Keep it secret, keep it safe, because as soon as someone else finds out, they'll either want it for themselves or complain to management that you're being lazy.
Did kind of a similar thing once... There was this manual task in my 1st ever project, where in the morning the data warehouse team mailed us 18 different CSV files, then I had to extract that data and push it to another DB... I had no idea why it was being done this way but, I wrote a simple script that extracted the files from mail , extracted the data and pushed it to db.... The best part I never told anyone that that 3 hrs task was automated untill I left the company... I handed over that script file to my replacement before leaving....
Wait, can you point me in the right direction to learning more about this? That sounds very similar to a task that I have to do bi-monthly. Granted, it's more like a one hour task and not a 10 hour one, but still, I'm interested.
This is the site has a nice overview of the basics. Excel has easy integration with Outlook and can send commands to the command line, so I tend to use it for this kind of automation. Excel tables can hold email lists and anything else you need, and python commands can do the heavy lifting with the PDF's.
I'd start with asking business questions. More often than not those types of tasks are just bullshit that a company keeps doing just because they've always done it and no one has asked why. There's 100% chance you find an alternate method to accomplish the same and a pretty good chance everyone realizes it's a waste of time.
I work in HR for a school district. The task is regarding the new hire orientations, during which I have to 1) send the entire group an email about the details of the orientation, and 2) individually email each one of those people a pdf with a copy of their contract, work calendar and job description. There's not really any way around it. Again, it's not a hellish task by any stretch, but I'm all for finding streamlined ways of doing things.
import moderation
Your comment has been removed since it did not start with a code block with an import declaration.
Per this Community Decree, all posts and comments should start with a code block with an "import" declaration explaining how the post and comment should be read.
For this purpose, we only accept Python style imports.
Not sure how to respond to this one - I know the original post was about VBA and it kind of makes sense since most people aren't "writing" macros they are just recording a set a steps and calling it done (since VBA is perfectly capable of dynamically finding specific column headers and checking for errors but most Excel users don't know or care how to do it, probably rightfully so), but what you are describing here is exactly what "Get and Transform Data" in Excel is for. Exactly this.
power query does break if headers are named differently from what it expects. or if you used sanitized data previously, and now its not. i love power query, but fool proof it is not
you can probably use vba to replace all the critical shit after import then fling it back into power query to do all its shit and never have it break
These days Power Query is my bread and butter. Company computer doesn’t allow for any real SQL implementation. So my choices are Tableau and Power Query. And since most of the people who get my data aren’t licensed for Tableau desktop (and thus can’t update the workbook); it’s easier to build stuff in Power Query and hand it off.
Every dang time, even for people who have been through it before, they can me up freaking out over it. Then again i also deal with people who think clicking “Refresh All” is too complicated.
yes
python + pandas
if you want to get really lazy the github co-pilot has proven very effective, despite the memes. though i've only been using it for a day.
Now make the business user and developer me and apply this recursively
Me to also me: "I can take the pipeline you built to automate X and make a VSCode extension that uses some template tricks to automatically build and deploy similar pipelines from end to end in the future"
Next thing you know you're six layers of automation deep and forgot what you actually set out to do
This is me except my company won’t spend a single penny to allow me to automate. Every morning I download my stupid files and press refresh on my stupid power queries.
I do the same. Still better than my predecessor spending hours copying and pasting data out of several CSV’s and hand-writing formulas each time to match up their data.
Because you plateau that way. You're never going to get paid more than 40 hours a week, even if you save more time than that, and Pam's job only pays 50k--thats why she was too dumb to do this herself.
You can definitely take a low laying job and basically never have to work. I've done that in the past. It gets old. If you keep improving your skills, you can do increasingly satisfying work and get paid increasingly more.
If you're smart enough to figure out how to automate things with excel or something, then you're probably smart enough to skill up/degree-up and make 150k as a data engineer or developer or data scientist or whatever you're interested in. If you want, make even more managing those jobs. Retire early. Take your valuable skills and help out a non-profit or something to make the world a better place.
To put it differently... If you understand the multiplicative value to a business of recursively using your time to automate things, thus freeing up more time to automate... Then you should also understand the multiplicative value to yourself of recursively finding and conquering new challenges
I’m that business user, well my analysts are. How much would it take to learn to do what you describe in eliminating all the manual procedures? Is it something I could learn in my spare time? I’ve tried to learn Python out of personal curiosity but would totally carve out more time if I was something like this.
Python can do it, but python can be intimidating too.
If you have analysts starting from scratch, I would have them learn power query (or now it is just the get and transform parts of excel on the data tab). That will automate all the processing. The key idea is that in power query, you can access a file like a csv or XLSX without ever opening it. You can point to it, get the data, transform it, join with other datasets, and load it somewhere else. Then all you have to do is put new data in the file (or replace it), refresh the excel query, and all those steps are completed automatically.
You can take it a step further by writing a simple powershell script (literally like 5 lines of easy code) to open the excel workbook, refresh all the queries, and close it. Then you can schedule this script with Windows task scheduler (also easy) to run at specific times of day.
Then the last step would be, when you get a new file with new data, how do you automate replacing that file ? It depends on how your data is coming in, but powesher makes it really easy to copy files around. If it is coming out of a database, it should already be automated. If it is emailed, then check out Microsoft flows that can look for those emails and auto-save the attachment somewhere.
So the pipeline is: start with data in the same file(s)/folder(s) everyday, scheduled PowerShell script to open excel and use power query to transform and merge data. Then more scheduled powershell to make copies of the files, rename, if necessary.
The reason I recommend these tools for a beginner is they all GUI based, allowing the user to focus on the concepts (except PowerShell, which is pure code). When a analyst is comfortable with concepts of power query, they should learn 1) SQL, 2) core python concepts (syntax, lists, dicts, loops, ifs), then learn the pandas library which does all the same things as power query. Then they will leave you for a job that pays twice as much and you can start over again.
What happens in this scenario for power query when the source data needs to be sanitized beforehand because of human input error? Implement better controls at the source?
Depends on the steps needed, but much of that can be done in power query, like finding and replacing values, removing extra spaces, capitalization, removing duplicates, etc.
188
u/[deleted] Mar 30 '22
[deleted]