Select Page

Eliminate unprofitable and unproductive work through technology!

William Wade
Dirigo Software Solutions

207-740-5709

EMAIL US

Eliminate unprofitable and unproductive work through technology!

William Wade
Dirigo Software Solutions
207-740-5709
wwade@dirigosoftwaresolutions.com

Power Query – Accountants and Bookkeepers Are You Using It?

You already have Excel open almost every day. It can be used for reports, reconciliations, or even a simple database. Here’s the question: are you really taking advantage of what’s built inside it? I recently have been talking with accountants and bookkeepers about their struggles with technology and found that few were aware of or taking advantage of Power Query.  Now, this is not a surprise as Excel is such a vast program, it is hard to be aware of all the features and they just keep adding more (Python integration, Copilot, etc…).  Let’s take a look at Power Query and why Accountants and Bookkeepers should know about it.


What Is Power Query?

Power Query is a data transformation and preparation engine.  It is integrated into Excel, but is not exclusive to it.  So, you could use Power Query in Microsoft Power BI.  Now, a few big words there, but simple it allows you to import data from different sources, manipulate them, and then load them to a destination (e.g. worksheet or pivottable).  I think the most important part here is the manipulation, as it allows you to do a lot of things.  For example, you could split a name into first name and last name or the reverse.  Do you need to get associated data, you can merge two spreadsheets.  So, maybe you have a list of employee and a list of hours, you can join those lists on a unique identifier such as an employee id.  The list goes on and on.  I won’t try to give you all the possibilities, but if you want to get more information I will leave some sources I use at the end.


Why Should You Care?

Ok, here we discuss the “So what!”  It is easy to blather on about how great it is, but why it matters to you.  Well, here you go, it saves you time.  Stop having to copy and paste data into Excel.  The value you provide is in analyzing the data, not manipulating it.  Especially if you need to do the steps steps over and over.  Built it one and refresh forever.  Reduce errors, because manual cleanup invites mistakes.  Next, you are probably already in Excel, you won’t need to leave it or pay for extra software.  Finally, work smarter not harder.  Time is a limited resource and you never get more.  Spend more time doing what you love and less on what you hate.


Enough Talk, Let’s Dive In!

Example – Cleaning Monthly Bank Transactions with Power Query

The Problem:
Every month you download a CSV file from the bank with your client’s transactions. The file has:

  • Extra blank rows

  • A column called Transaction Date stored as text instead of a date

  • A column called Amount with negatives for expenses and positives for income

  • Headers like Acct_Desc that aren’t client-friendly

Normally, you’d spend 20–30 minutes cleaning it up.

Data for this example:

Acct_Desc,Trans_Date,Description,Amount
Checking,01-03-2025,Amazon – Office Supplies,-45.60
Checking,01-05-2025,Payroll Deposit,2500.00
Checking,01-07-2025,Starbucks – Coffee,-6.25
,,,
Checking,01-10-2025,Verizon – Internet,-89.99

Copy this text and save into a file.  I called mine “bank_transactions.csv”  FYI, csv stands for comma-separated values.


The Power Query Solution (5 minutes to set up):

Load the CSV into Power Query

    • In Excel, go to Data → Get Data → From File → From Text/CSV.

    • Select the file and you should now see a preview of what is in the file

.

    • Click Transform.  You will now be taken to the editor and you should see the Power Query Editor.

Clean the data

    • Remove blank rows (Home → Remove Rows → Remove Blank Rows).  You will need to click on the Remove Rows button to expose the Remove Blank Rows.  You should now only see four rows.

Here is what the results should look like when done.

    • Rename columns: Acct_DescAccount Description, Trans_DateDate.  Double click on the header name and it will allow you to rename the column header. 
    • Change column type: Make sure Date is Date, Amount is Decimal.  If you look by the header, it shows you what Excel thinks the column type it.  ABC for text, a calendar image for dates, and 1.2 for decimals.  There are other possible types, but for now, just make sure you see those types.  If not, you can right click on the header and select Change Type and the correct type. 

This image shows you what the Change Type menu looks like if you need to use it.

 

    • Split description: If Description contains “Vendor – Detail”, use Split Column → By Delimiter to separate vendor from detail.  Select the column and in the header click on Split Column.  And just like Remove Rows, clicking on it will show you more options.  Click on By Delimiter.  Select “-” as the delimiter and click ok.

Load back into Excel

    • Click Close & Load. It will now load the data into a worksheet and set it up as a table, ready to be used.

    • Now you should see the end result.

Next month?

    • Download the new CSV into the same folder, ensuring to save it with the same name.
    • Open Excel and hit Refresh. The cleanup steps are repeated automatically.  No fuss no muss!!

Result:
A clean, professional table in seconds instead of repetitive manual work.  Additionally, you can use this query with regular Excel functions to create dashboards or feed reports.

Small effort up front. Big payoff every month.


Wrapping It Up

Power Query isn’t new, but it’s one of the most overlooked features in Excel. For accountants and bookkeepers, this can save you real time and effort:

  • No more wasting time on repetitive cleanup

  • No more error-prone manual edits

  • Just fast, consistent results every single month

The payoff is simple: less time fixing data, more time analyzing it and serving clients.

What Next?

If you’re curious, Microsoft has a great Power Query Overview to help you get started.  Also, Leila Gharani has a great series of courses on Excel, covering a lot of different topics.  Here are two I would suggest for Power Query:

Excel Made Easy With Power Query

Automate with Power Query & Power Automate

Don’t worry if it looks a little technical at first—most accountants pick up the basics quickly. And if you hit any roadblocks, I’m here to help.

In the next post, we’ll walk through how to combine multiple files in one click using Power Query. Spoiler: it’s easier than you think.

Above all remember life is too short to waste on work you hate!

RECENT BLOG POSTS & ARCHIVES

Why Automate This Next?

Mike Michalowiz is a popular author of the book Fix this Next.  In it he details that business has a hierarchy of needs like humans do.  I just finished reading the book and it got me to thinking that automation also has a hierarchy of needs as well.  If you fail to create a great foundation, you won’t be able to move to higher and more sophisticated levels of automation in your business.  So, it compelled me to create this blog so I can help others to build the solid foundation they need to help them create the automations that will eliminate the unprofitable and unproductive work that keeps them from doing what truly matters to their businesses and themselves!

ARCHIVES

CATEGORIES