QuickBooks to Excel: Clean and Organize Your Data in Minutes

Описание: What You’ll Learn:

In this tutorial, you'll learn how to transform messy QuickBooks data into clean, structured accounting records using Power Query in Excel. If you've ever exported a general ledger and found it full of missing headers, empty rows, and strange formatting—this step-by-step guide is for you.

We'll cover how to:

Load and preview your QuickBooks CSV file

Dynamically find and promote the correct headers

Clean and standardize category names

Fill down missing data

Add custom columns like “Category Path”

Set accurate data types and organize your layout

With these steps, your financial data will be ready for pivot tables, dashboards, or reports—no manual cleanup required.

Chapters:
Intro: Why QuickBooks exports are messy
Load your CSV into Power Query
Remove automatic type detection
Identify correct header row
Promote headers dynamically
Rename columns and remove extra rows
Fill down missing category values
Clean category names with Text After Delimiter
Filter for real data only
Create Category Path column
Set correct data types
Reorder and sort your final dataset
Automate future cleanups with one-click refresh



📝 Get the Full M Code:
let
// 1. Import CSV file (QuickBooks General Ledger)
// - Fixed 7-column structure
// - UTF-8 encoding with commas as delimiters
Source = Csv.Document(
File.Contents("YOUR_FILE_PATH"),
[Delimiter = ",", Columns = 7, Encoding = 65001, QuoteStyle = QuoteStyle.None]
),

// 2. Find the row number where the first column equals "Category" (used as header row)
Custom1 = List.PositionOf(Source[Column1], "Category"),

// 3. Skip to header row and promote those values as column headers
#"Promoted Headers" = Table.PromoteHeaders(
Table.Skip(Source, Custom1),
[PromoteAllScalars = true]
),

// 4. Rename blank and inconsistent columns
// - Rename "" to "Category Name"
// - Rename "Memo/Notes" to "Memo"
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers", {{"", "Category Name"}, {"Memo/Notes", "Memo"}}),

// 5. Remove one redundant top row (QuickBooks export formatting)
#"Removed Top Rows" = Table.Skip(#"Renamed Columns", 1),

// 6. Replace empty strings with nulls in key columns
#"Replaced Value" = Table.ReplaceValue(
#"Removed Top Rows",
"",
null,
Replacer.ReplaceValue,
{"Category", "Category Name"}
),

// 7. Fill down blank cells in Category and Category Name
#"Filled Down" = Table.FillDown(#"Replaced Value", {"Category", "Category Name"}),

// 8. Extract text after the last "- " in Category Name (cleaning hierarchical labels)
#"Extracted Text After Delimiter" = Table.TransformColumns(
#"Filled Down",
{{"Category Name", each Text.AfterDelimiter(_, "- ", {0, RelativePosition.FromEnd}), type text}}
),
// 9. Filter out rows where [Date] column is blank (remove header/footer artifacts)
#"Filtered Rows" = Table.SelectRows(#"Extracted Text After Delimiter", each ([Date] ＜＞"")),

// 10. Create a combined category path (e.g., "Expense ＞ Office Supplies")
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Category Path", each [Category] & " ＞ " & [Category Name]),

// 11. Convert data types for key columns
// - Date → date type
// - Amount → currency type
#"Changed Type" = Table.TransformColumnTypes(
#"Added Custom",
{{"Date", type date}, {"Amount", Currency.Type}}
),

// 12. Reorder columns for readability and consistency
#"Reordered Columns" = Table.ReorderColumns(
#"Changed Type",
{"Date", "Category", "Category Name", "Account", "Payee", "Memo", "Amount", "Category Path"}
),

// 13. Sort the table by Date in ascending order
#"Sorted Rows" = Table.Sort(#"Reordered Columns", {{"Date", Order.Ascending}})
in
#"Sorted Rows"

🔔 Subscribe for more tutorials on Power Query, Excel automation, and accounting workflows.