U Love Office
Home › Blog › Excel & Data
Excel & Data

Power Query in Excel: clean messy office data automatically

✍️ admin 📅 16 November 2026

Quick answer: Power Query is a free tool built into Excel (Data tab, Get & Transform Data) that cleans and reshapes data using recorded steps instead of formulas. You load a messy file, such as a bank statement export or an ERP report, then remove junk rows, split columns, fix dates, trim spaces and change data types. Power Query remembers every step. Next month, you drop the new file in and click Refresh, and all the cleaning happens again automatically.

If you spend the first morning of every month deleting blank rows, fixing dates and splitting names in the same report, Power Query was built for you. It is available in Excel for Microsoft 365, Excel 2016 and later on Windows, and in recent versions of Excel for Mac.

Diagram of the Power Query process: get data, transform, load, refresh
You build the steps once; Excel repeats them forever.

Why Power Query beats manual cleaning

Manual cleaning has three problems: it takes time every month, it depends on one person remembering the steps, and mistakes are invisible. Power Query fixes all three:

Our example: a messy bank export

We will clean a typical bank statement export that many UAE businesses receive as CSV. The raw file has:

Raw bank statement CSV export in Excel with header rows, blank rows and extra spaces
The kind of export that usually takes 20 minutes to tidy by hand.

Step 1: Load the file into Power Query

  1. Go to Data > Get Data > From File > From Text/CSV.
  2. Select the bank export.
  3. In the preview window, check the File Origin encoding. If Arabic text looks like random symbols, choose 65001: Unicode (UTF-8). Our guide on fixing garbled Arabic text in CSV files explains this further.
  4. Click Transform Data (not Load). The Power Query Editor opens.

Step 2: Remove the header junk

The real column headers are on row 4.

  1. Home > Remove Rows > Remove Top Rows, enter 3.
  2. Home > Use First Row as Headers.

Now the columns are called Date, Description, Amount and Type.

Step 3: Remove blank rows and the total line

  1. Click the filter arrow on Date and choose Remove Empty.
  2. The closing balance row still has text in the Date column. Use Home > Remove Rows > Remove Bottom Rows and enter 1, or filter out values that equal “Closing balance”.

Filtering by value is more robust than removing a fixed number of rows, because next month’s file may have a different footer.

Step 4: Fix the data types

  1. Select the Date column. Go to Transform > Data Type > Using Locale, choose Date and locale English (United Kingdom) or Arabic (U.A.E.), so DD/MM/YYYY is read correctly.
  2. Select Amount and set the type to Decimal Number. If commas cause errors, use Using Locale with English (United Kingdom).

Using a locale is the key trick for UAE data. Without it, Excel may read 03/10/2026 as 10 March instead of 3 October.

Six Power Query steps used to clean a bank statement export
Each step appears in Applied Steps, in this order.

Step 5: Clean the text

  1. Select Description. Go to Transform > Format > Trim to remove leading and trailing spaces.
  2. Then Transform > Format > Clean to remove invisible characters.
  3. To collapse double spaces inside text, use Transform > Replace Values, replace two spaces with one, and repeat the step once more if needed.
  4. Optionally use Format > UPPERCASE so “Client A” and “CLIENT A” are treated the same.

Arabic text is handled the same way. Trim and Clean work on Arabic descriptions too.

Step 6: Split amounts into Debit and Credit

Accountants often want separate columns.

  1. Add Column > Conditional Column. Name it Debit. If Type equals DR, output the absolute value of Amount; otherwise 0. (Use Add Column > Custom Column with if [Type] = "DR" then Number.Abs([Amount]) else 0.)
  2. Do the same for Credit with CR.
  3. Remove the original Amount and Type columns if you no longer need them.

Step 7: Load and use the clean table

Click Home > Close & Load. Excel creates a new sheet with a formatted table of clean data. You can now build a PivotTable, use SUMIFS or match against your ledger.

Clean bank data table produced by Power Query with separate debit and credit columns
The same data after Power Query, in under a second on refresh.

Next month: refresh in one click

This is where Power Query pays off. When November’s statement arrives:

Better still, save every monthly statement in one folder and use Get Data > From Folder to combine them all automatically. We explain this in our guide on how to combine multiple Excel files into one master workbook.

Getting data out of PDFs

Some banks and suppliers only send PDFs. Power Query in Microsoft 365 for Windows can import tables from PDF files via Get Data > From File > From PDF. If that option is not available, convert the PDF with PDF to Excel first, then load the Excel file into Power Query. For scanned PDFs, run OCR PDF before converting.

Hands working on a data spreadsheet on a laptop

Other office tasks Power Query handles well

Common problems and fixes

“DataFormat.Error: We couldn’t parse the input as a Date.” The date format does not match. Use Change Type > Using Locale with the correct locale.

Refresh fails after the bank changed its export. A column was renamed or added. Open Power Query, look at the first step with an error and adjust it.

The file path changed. Edit the Source step or use a folder query.

Numbers load as text. Check for currency symbols or spaces. Use Replace Values to remove “AED” or spaces, then set the type.

Arabic text is garbled. Change the File Origin to UTF-8 in the Source step.

Do and avoid list for building reliable Power Query cleaning steps
Robust steps survive small changes in next month's file.

Sharing a Power Query workbook with your team

A Power Query report only saves time if others can use it when you are on leave. A few habits make that possible:

Power Query vs formulas vs macros

Formulas such as TRIM, TEXTSPLIT and DATEVALUE can clean data too, but they live alongside the messy data and need copying down every month. Macros (VBA) can automate cleaning, but they are hard for most office staff to read and are often blocked by security settings. Power Query sits in between: no code, visible steps and a refresh button. For recurring imports, it is usually the best choice. For one-off fixes on a small sheet, a formula is fine. Our guides on the Excel TEXTSPLIT function and on splitting full names into columns cover the formula approach.

📘 Continue reading

Frequently asked questions

Is Power Query free?
Yes. It is built into Excel for Windows (2016 and later, and Microsoft 365) and recent Excel for Mac versions.

Do I need to learn the M language?
No. Everything in this guide uses buttons. M appears in the formula bar if you want to learn it later.

Will my colleagues be able to refresh the file?
Yes, if they have access to the source file location. Use a shared folder rather than your Downloads folder.

Does Power Query change my original CSV?
No. It only reads the source.

Checklist for building a first Power Query data cleaning workbook
Tick these and your report will refresh reliably.

Conclusion

Power Query turns a monthly chore into a one-click refresh. Load your messy export, record the cleaning steps (remove junk rows, set types with the right locale, trim text, add the columns you need) and load a clean table. Next month, replace the file and refresh. Once you have done it for one report, you will find a dozen more to automate.

Last checked: 9 November 2026.