Auto and Manual Categories

Hey Einomi ,

I obviously try my code here I attached the screenshot, the excel file, and the pbix file for your easier understandings. It is a kindly request not to judge people, I am here to help you just and spend much time on this.🙂

The Excel File

The pbix File

Before you conclude that I need to be clear about your requirements. You're building a Power Query solution that merges both automatic and manual categorizations of financial transactions for streamlined reporting. You download bank statements in CSV format, transform them in Power Query, and add extra columns like Category and Subcategory manually in Excel.

If your requirements are those, follow the below steps:

M Code (Power Query)

let
// 1. Load the transaction table from SharePoint or file
Source = Excel.Workbook(File.Contents("C:\Users\NasifAzam\Desktop\New folder (2)\All Tables.xlsx"), null, true),
Transactions_Table = Source{[Item="Transactions",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Transactions_Table,{{"Date", type text}, {"Counter Party", type text}, {"Reference", type text}, {"Amount", type number}}),

// 2. Load the mapping table
MappingSource = Excel.Workbook(File.Contents("C:\Users\NasifAzam\Desktop\New folder (2)\All Tables.xlsx"), null, true),
MappingTable = Source{[Item="Mapping",Kind="Table"]}[Data],

// 3. Auto Map Category/Subcategory using Mapping Table
AutoMerged = Table.NestedJoin(
Transactions, "Counter Party",
MappingTable, "Description",
"Mapping", JoinKind.LeftOuter
),
ExpandedAuto = Table.ExpandTableColumn(
AutoMerged, "Mapping", {"Category", "Subcategory"},
{"AutoCategory", "AutoSubcategory"}
),

// 4. Load the manually edited table (same structure as output table with manual overrides)
ManualSource = Excel.Workbook(File.Contents("C:\Users\NasifAzam\Desktop\New folder (2)\All Tables.xlsx"), null, true),
ManualEdits = Source{[Item="ManualEdits",Kind="Table"]}[Data],

// 5. Merge auto-mapped + manual edits (manual takes priority)
FinalMerged = Table.NestedJoin(
ExpandedAuto, {"Reference"},
ManualEdits, {"Reference"},
"Manual", JoinKind.LeftOuter
),
ExpandedFinal = Table.ExpandTableColumn(
FinalMerged, "Manual", {"Category", "Subcategory"},
{"ManualCategory", "ManualSubcategory"}
),

// 6. Determine Final Category and Subcategory (manual wins)
AddedFinalColumns = Table.AddColumn(ExpandedFinal, "Final Category", each 
if [ManualCategory] <> null then [ManualCategory] else [AutoCategory]),
FinalWithSub = Table.AddColumn(AddedFinalColumns, "Final Subcategory", each 
if [ManualSubcategory] <> null then [ManualSubcategory] else [AutoSubcategory]),

// 7. Optional: Remove temporary columns
Cleaned = Table.RemoveColumns(FinalWithSub, {"AutoCategory", "AutoSubcategory", "ManualCategory", "ManualSubcategory"})
in
Cleaned

Step 1: Prepare Excel Tables

You’ll need 3 named tables in Excel:

1. Transactions contains Date, Counter Party, and Reference Amount column

   

2. Mapping contains Description, Category, and Subcategory column

   

3. ManualEdits contains Reference, Category, and Subcategory column

   

    

Step 2: Open Power Query Editor in Power BI

1. Get Data → Excel Workbook → "Select the excel where you create all the tables"

2. Select the 3 tables

3. Go to transform

4. Create a new Blank Query and paste the M Code above (Change the file sources as per your needs)

5. Click Done

   

The Final Result:

Step 3: Refresh & Maintain Edits

• When you refresh, Power Query will:

  • Auto-map what it can using the Mapping table

  • Respect any manual changes from the ManualEdits table

• This means manual changes will persist even after refreshes.

Step 4: Output for Reporting

• Load this final query as a table into Excel

• Use this table for PivotTables, charts, or Power BI

Best Regards,
Nasif Azam