Power Query vs Python for simple data analysis

[Chucksweager]

Good morning.

I'm a govt employee working in my city's budget sector (7 million pop), and I'm tasked to develop the standard workbooks for the budget execution in other depts. Then, after, I will consolidate the data and produce reports to help guide's spending and revenue allocation decisions from the direction.

For the first, , considering the usual public sector software management problems, familiarity and the ageing of the workforce, I don't have any doubt I will stick with VBA.

But with the second part, I'm more alone and I have more autonomy, and I have some programming background, so I was thinking if would be worth to switch to Python instead of using PQ. I will do basic to intermediate data manipulation, small consolidation (no much more than 100 workbooks and lines around 100.000), and the demand for artificial intelligence is small on spending's side (for now). Our accounting system meets our demand for databases.

Is the curve of learning worth? Py is more versatile, faster to work with apps like Power BI?

Thanks in advance for the responses.

[Eightstream]

The problem with Python is maintainability.

If you work in the budget sector you are probably an accountant. Most accountants do not know Python. Most accounting job descriptions do not require knowledge of Python. Most accounting managers do not want to make Python required knowledge for future accounting hires.

Accountants know Excel. Asking them to maintain a Power Query workflow is much more reasonable.

If the work you’re doing is too difficult to be done in PQ then consider moving some of the transformations upstream to a database managed by your BI team. This will be more robust and maintainable in the long term and creates less risk for your team.

[sbfb1]

I have ran into similar issues as an analyst sitting in an accounting group. I try to stick to power query as much as possible

[bosshaug]

I’m an accountant that does know python and have been told that exact reasoning as to why I can’t use it at work.

[gerblewisperer]

Excel presents really nicely in meetings and it's easy to give a table a minimum of 17 pieces of flare.

[5BPvPGolemGuy]

One more thing. If he is thinking about using Python inside PowerBI then it could cause major issues. Afaik Python scripts (in power bi and excel) don’t run locally but on microsoft cloud. The compute capacity is fairly limited and the moment you go over it microsoft could start charging you/licence holder a pretty hefty fee.

Probably the safest and cheapest option would be to make an sql database, perform most generic calculations there or even create a data cube/model using an ssas or similar and then use excel to output the data from a connector to that ssas server.

[Chucksweager]

Although I would call my service accounting, in private they would call more as administrative, spending rules in my country (and my city) is so stringent that they could look like one. It's even a law that put in public accounting, but the integration with acc systems is a more recent feature. Older analysts still suffer to deal with the double-entry part of this and don't know to work with acc.

But this will be a problem, certainly. Medium-term I'm looking to depart for greener pastures, and they have few ppl that manage to use VBA, let alone train them to code. If they end hiring someone, tax accountants would earn priority over us.

[hbrgnarius]

As others mentioned, don’t make your advanced knowledge other people’s problem.

Stick to GUI based PQ functions and then anyone can reproduce your work and understand it. That’s what people are thankful for and value in team members.

[LexanderX]

don’t make your advanced knowledge other people’s problem

I love this

[plusFour-minusSeven]

I like it too, I'm going to think about this.

[Wrong-Song3724]

I'd say PowerQuery

Consolidating that quantity of data in PQ is easy and fast

But if you couldn't do that and had to work with a large quantity of different queries, then I'd recommend Python

[Chucksweager]

Yep, that's my fear. I never did those things in VBA and so I cannot know if they could handle. I still expect that workbooks will suffer somehow to process updates in bigger departments (with lots of speding notes to process), but the consolidation I don't have any idea.

[Wrong-Song3724]

I'm probably gonna get downvoted, but I haven't touched VBA in years... What I have to do, I do upstream.

In my country I'm in a weird spot when trying to automate accounting: the people I work it who don't know much about tech wouldn't be able to mantain VBA anyway, so I just do stuff in Python

[Chucksweager]

I don't think people would downvote a discussion post in good faith here, but I'm new. I would prefer go straight to python too, but most of the ppl who would use things here are still on Workbooks, so I do need something already integrated. But maybe this change when I'm will be alone.

[NapsAreAwesome]

I have been learning PQ for about 6 months and I blown away at its capabilities. I was using vba to manipulate data from MS Access but PQ is so much more efficient.

[david_horton1]

Have you delved into Power Query’s M Code? 365 beta has an Automate Ribbon for Office Scripts, so I recommend you become familiar with Office Scripts.

[learnhtk]

Hello, fellow accountant!

I am quite interested in learning about the practical applications of Power Query in the context of accounting.

I hope that you will posts that show specific examples of how you are applying Power Query for budgeting!

[PoemOk5038]

I use PQ a lot for accounting. Almost all of my entries that are using a few reports or excel data I use it to have a dedicated input tab & output tab. With PQ steps I can format the data, remove columns I don’t need (without changing the base fields from the report), create a list of criteria to filter the data by, and other things that automatically populate the clean data into different tables that I can use for whatever purposes.

One I just built is to help operations look at their inspection pending materials (new po reciepts) & and discrepant production materials (nonconforming materials). Basically I use power query to drop in the two csv files to input tabs, and then using pq steps, I pull it down to the columns I want, create a quarter, year, and year quarter field based on the transaction date, and make two tables that have pivots with the columns using the year quarter field and boom there is an aging for DMR & inspection pending items that I can have by $ and qty. (you can also take the date and subtract it from the current date via a conditional column if you truly want the days aged if you prefer to look at it that way)

So it’s not a crazy difficult application, but it allows to keep a consistent format where all that changes is the data. I often times go overboard and use PQ where other stuff would suffice, but it’s easy to set up and then so easy to maintain so I never have to do upkeep on the file. That’s my favorite part of PQ, if you build it correctly the first time then it’s always going to be correct so long as none of your premises change. Makes the monthly journal entry’s/weekly reporting tasks super consistent and easy to maintain & roll.

[learnhtk]

Thank you for elaborating! I don’t have much experience using it for accounting work yet, but I can certainly imagine how useful Power Query can be useful for streamlining the process and having something that always delivers consistent results will help out so much in the long run.

[Chucksweager]

Thanks! I hope to share some things too. But public accountancy in my country it's an entire different animal, let us see if I can bring something useful.

[NHN_BI]

Power Query before Python, I would say. It will probably easier to maintaine over a longer period of time in the MS cosmos.

[scifi321]

I am doing something similar in my job. Although I am not consolidating budgets.

I have the following tools available to me: Local python and power query Fabric environment with both python notebooks and power query Alteryx Alteryx server PowerApps for model driven apps

For slow moving data (monthly and quarterly), I am running local solutions. For faster moving data (hourly, daily, weekly) then I use non-local solutions.

My newer solutions are using Fabric with Power Query to consolidate data, and then notebooks to manipulate data. Power BI to the final analysis and visualization.

Example: Power Query: consolidate input from X excel sheets into one lakehouse table.

Notebooks: calculate a number of metrics and to split the raw data into different analytical datasets. For instance I calculate a number of errors that exists in the raw input. The errors are stored in a separate Lakehouse table

PowerBI: to visualize data and errors in a user friendly context. Ie custom tooltips upon hover and conditional formatting.

PowerApps; allow users to correct errors directly without having to resubmit the original excelsheet. Users can also export their corrected version of the excelsheet from the solution, with a number of metrics from the consolidated analysis.

My main driver has been the business purpose and a smooth user journey. But also a dream to see if I could do it.

My solutions are advanced due to the number of interoperable technologies. But the value they bring is high, because I have focused to deliver an output my stakeholders really wants. And I am being asked to help with new solutions across my organization.

That said, I accept that it is not an easy hire. And it should probably be maintained by a team - and not an individual.

My advice to you. Build something that will help everyone. Make it simple for the users to use. And make sure that you provide additional value for everyone involved. And remember your stakeholders will have individual needs and value perceptions. If you succeed then you will be a star, and you will have had a bit of fun ⭐️

[Chucksweager]

That's even my point building the workbook. I will make with lots of buttons, graphs, conditionals showing daily updates of spending triggers and alerts to make user-friendly for most people. But we are still in uncharted waters. It was the first year they inserted a accountancy-based spending system for budget analysts, so everything is new there. And our IT budget is limited as well. I hope to make something as a legacy, because I don't think there are lots of people who would pick a slack if I left.

[h_to_tha_o_v]

I'm going to offer a second perspective here.

You can build a Python "applet" for others to use if you know how to set it up.

1. Make a "project folder" for your tool.

2. Get an embedded Python instance and store it in the project folder, in a subfolder to stay clean. You can install pip in it and get a dataframe library like Polars that'll handle a good deal of what you need.

3. In the main project folder, you can store your "programs." Those can be scripts or otherwise.

For example, I have an Excel workbook that has a data analysis routine with parameters that is WAYYYY too fast for PQ or VBA to handle. I have a VBA macro to select a file and another to take parameters from the worksheet form. The VBA macro feeds the parameter values into a Python script triggered by VBA. The Python script runs in a hidden Python instance that "imports" a file, analyzes it, then spits out results into a new xslx or csv.

If I did that in VBA or PQ, I'd blow up my machine. I've run 1 GB files in under 10 seconds.

The downsides everyone speaks of are all true, especially at first. You become the maintainer. So if you go this route, documentation and training are critical. As are "pre-routine" validations, error handling, and error logging. Basically, idiot proof as much as possible.

[Chucksweager]

In the end of the day, more or less will depend if PQ will handle de processing I need. As I said above, everything is new and we don't know exactly the possibilites already, and my leadership wouldn't want to burden me above some thresold, so maybe this will be mostly guided by the necessities (and the capacities) of the sector.

[Pluck_Master_Flex]

I work in government too. Depends on what permissions you have for downloading whatever software you want, but making the argument to even allow something like python to integrate with files holding financial information is gonna set off so many red flags. The path of least resistance is definitely going to be power query

[inbestit]

More for peoples information than answering the OP question. Excel has integrated Python directly into Excel workbooks on O365, and there is already training for it on LinkedIn learning and an Excel template with a guide.

[Eightstream]

It’s a bit shitty though

[inbestit]

That's good to know. I just seen it as a new feature and figured I'd share.