Auto and Manual Categories

[Einomi]

Hi,

I download bank statements in CSV formats, and consolidate them in PQ. These are my steps

1. Load folder from SharePoints (using SharePoint.Contents)

2. Make all my transformations then load to Excel as table

3. Add a few columns in Excel, among them Category and Subcategory

4. I create a mapping table in Excel (attached) then load it PQ
5. I write some M code to reconcile some transactions automatically with Category and Subcategory.

My issue is, that I cannot map all transactions automatically, and I need some manual input and need to reconcile Category and Subcategory manually for the ones not able to do it automatically 

What I need

• all Category and Subcategory (manual and auto) should sit on the same column for reporting purposes
• leverage Table.Buffer
• optimize as much as we can performance, I do not wish to have multiple merges between queries

I give sample transactions

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVxNjxy5Df0rDZ93tCKpz6MNb4IsECCIg1ycPXTs9u4g42ljZtaJ8+tDzbRUKqsmFlm+jV1dbH08Pj6SUr99+wLcjzb8iBbdix9e/PF8/vXmxH/85fjl4+n24XB3ene6/nx6f/hwd/54aI8xeTSUXvzyw9sXGDsDPx9vT4c3H68ffuN/vDneHO++HD5dbD2cD6vHV+BtNPbJSD+Klx+P/z3fPjuK9pgI0OT8aAByb+DTp/8zi/qUQsgmwOPrtn/9r+d/nu4eDq/uzv++3ZzGVx+4ggz2MhGbOkN/O93fHJ8dR30KwG/D0zQwdK//9PH65svh9fHz9f3mMNbPryhbqKPo9+TlzfW70+Hn82+39+ft+Xz9iSsIsU5IhQ8CH4x/mpLFHh/8HYfX59M2OpaHPAJIdQT93vz9/P744XxbPvOn28/nMu7OQvf0CmJwxrunZUWVCQSbjMOnaYR9S4oRXQW7V3tMtHbLiGI8RITVlNWDDm3OFXRet08hWONGFhBsExX3g3Gnp9GGzoYttL28vnt1++p5JqmP0YM3aYOJZh3GxUKFNK7Cm+Ovz6zA5Qm7fTB0YTGNr0LIZBC/Bw0maBBPeyBFbissiDyFI1Tlj9Wkvr2egSeR49PXkyIqJceh8el9AA13UvQNzU4BRgIgk54MQNSA0Scy7gKpfgleHe/e3Ry/3D/+efuvw4fT6b4sXff/VwAmPQkDC3vAhD776pJR49PEENhUF7PLiCwuwshsGq512Gi7n8vrf/xuLYY3hz9c3z2Dyq8/wlqDw1rCMQb8+frd3fn+/OFh21D/+ApcBhP99xlRCBf6Xjmaam4pZuOejIGKyMnHyh0r+GlG4zyvURoROEsEQMkZwFFFzclTRA4JFwCuFuPl7++vHw4//ecT+8v2VL76BDMqeyXCyEnzATKmqi0hiKfiYsqVFcBqMet5Py6acjdmHasWCHvkZeCIH1LTT064IoFq0C7f7+RAtzl2usvJJ4DIGr86m9OZIG8yNd93Ug9Bwtzra6eOFmST7aTHDkMOm24gxZQoLjlp0aLLzkrUC3lqCWUQgwvcMgbbr8a3xQ8U2eRyiy5OTzop8kLmpkvFK+mQc8iLEIN+FrMBnJO3ZmC1DOK8mloq2kNCFeH8ki5F+cYGjv3kR5edKzRQdL6G6qSgLMehNbZ45FQkHjnZqLSJ+j3BnFKXCOu8DIuUumysVwDMIUUT/EjCs0I7cKoANLLW7PsZXS1ggdTPIZjkxgA0WbLy1uSl/KeCQoFy3NjB6QpR9NWVkHYxFXqGQWr1N0UgTDZUv0RQWQBmBuNhxIKGZZBY3rs4etnc7jrHhJf9GNcl2+sxmujG/Z3AZg5oIo77MclTjr0ib3iFpCTFjmk3vGNa2DjvTYSN4CcPoymaRKMgmcrFgwnUirBKKYJElSRRwVGOw2cDQm9AU0MFv03Ys5LCuWTQ69Ud2MzA2HD0qd2IxtOIqXlYOo55yW+spBhU7GJNeK+1ldwUMfdVrmGo+3lH96Xw6Fr49PpB+NjiUUlPvYwxMEZXu1m2f31mV1OdfHExr3IxxKUU78Sj58ixArWXeiiWAnytU/QG5pV65lwYG9vpVgHSUoO3u9AAwOtJG5YUlbsYoavcLaZmJWIOS2+vX1tNWCcXa1wsxS3xYBLnyGEDKLJ4AEsG0++1vMAbIGyhfh62iK3n4lSwZcmFi+Tag7jSagwjDUzmZYihhYZ+YwSVGJejgUWy6beFcqauLyhfVSjtizC6jKw+ja2D4eXr6UrbNoyBRchHVPFpk8LbMDO+3cauyrcEWtd3Fd0m1bz1JsUNCpuKLj6kthN5J3+BYwKrBZmw1xhbuxQzVouiAHzErX2eFrgYs4lLXUQMFECb+8KKSH6UZmUaSVTmbAwv50cjAvqxOTQ9SepxRFbIaYzbksQtt6IC6iw4pKZlduEKy/EiO3KYsNIFpdFgxykJ6iRLI3Wv0z0Wt8exTAEVDcAoG1SuHx20gk3ULy066k99aM0wgaSuFQPCckXG5RQada/PsTsGy6IwtO0FabxGX4qQqa0CSPmPAgd8DOMEZDmQo/7syDINuRbz1FRykG9HZJGcxuXUdKRiF1bEy8obm03GjY2dDSuUmAyXNEi+sZGpsLoZdAa+ndmj887g0kYCTTygnGp1fAVMMYHGKmiLNhfigTzW4F6oZnlbUb16bKC08wTa+eByxKFEaxCRcGiQ8nvAzflOfxxAtb1IRZhS64WBPFgniLVZuXJ1SQebxV/Xxhd7GZbyJm2gVFW3t8Eacq0sJyZzKOx1kZJWCg5fa/7qLSUOhD5pqRc8QM2iV0s5WVwNaeS6iW5DWTIK45pPvEppOSKmoJaYbavP5z3uiN5uuqOUWVIOW2amT8z1bbCeX6bLIN6E8e2ZjhGvZD2IksRfzB+27TROr2AUrcRVGrSLDFhWmhBGZpqIvpYzUo9jiNDwfOiKfXINwll6o9cdyCRqOcwqCquyB8tRsB6Gs0qmezyMRuF7jSg04lshX5C2EtXusTISBqpN0+JBqAilJRgvSRlKY5dzzKbgmvMsBhRSi9r5qQK7ZkqWkljou42owQki5720HOZGAakxq5vlHLnkzXI7qzbWnGIrHkvroTUtUM8fMVAXUZZ9mD53CsvZK9sZmJIEuV7/KcFZPwlC77tMDmVR3rHojzQOQ1Lbj63tu/ILcXRidRlz0xuoJePUOkCQZcDMDIdamE9Kl4JyOgXiuB7Tea1ntWA33ENwXcj1xYo9TEX86ia4prP8cg6ZWhdsz1g4qG1663QuBJQNUquEiQ2Q5WzMLQKyGZjsdPiMrb+QtYydazMdhHwdy5kfbNVRIU8QsG8sp111IYuwAXO1AVN0mdq1JyuaeGbtQa2sIZw25BSbVrXaecNyudLSHmazYcsZpRUn72hrNJMoziUNCuPrgssalroKkXZJV2UmadTLiUV2Gp1BdliYRe3I8wLx0G6AWfU6PLeYMy6VL5VkuzNIQIHExkQ0xXHb9mUVvHXHTJlt/IaxackfOPGhDcqczNsT5+24HIjR8b2jaPJyPpHkhfpy3XKpepHQU3z0tWhSyqDi73fRJ0O5aWQSMDePHHCc+ux1Ag54NdxCP3GFDAqxv65LosDVSn/Yj0ICggTVVaH/dnmqkJZ7O74zpLsq4qAWg0sc0I8Kgm/ZcG9I0kJuit/tW59c1W4ROaRiZFqms9ot1SIThppMrJxnUr1g8FW9QFSCDxzGLseV8od1tWq6QslkT74eUELl4AMnY7RhQ1W0ibHrjzVTksMbLrVTcL3/Td/eywyHpY68azbR4RajTfwAQWm7Lr+dImXlci/UjXQobyp05a8+rEmbCtT0k93FrUjtxiuuwqz8SgVBaxtmscMhMkzRjwsjPO8AbTawazZYTjtXvEQR0pBCaj+R43eFmBjsVoSYblx6i/VyN3g56EPEdshx10QI42YEl0Uo2wopqx2ZTQAtq5HQUg7h6+AptDZq3OG4sPwGFKromF+AejZp5WjStDwsd91lAAfWCvWe/8rNdCdOOae9nGxeg3wyJWaJG90YXGYidewv/qi2IrRe88qE5nYFM1fJun75Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Counter Party" = _t, Reference = _t, #"Amount (GBP)" = _t])
in
    Source

 

the result should be something like this

where I have a single Category and Subcategory column, and for example the highlighted row need manual input, I want to be able to add manually the Category and Subcategory and still be able to refresh queries without losing all the auto ones. Please a code that optimizes the performance

Thanks for your time and expertise.

@ronrsnfld @lbendlin @MarkLaf @SundarRaj @Nasif_Azam @speedramps 

[Nasif_Azam]

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

[Einomi]

Hi,

I am sorry if you got offended I did not say you used AI (which could be fine in some cases) I said it it very similar because the style of the posts and the style of the code along with Table.AddColumns were a bit confusing.

I am not sure why you have a third table called ManualEdits. I only work with two tables, the one I download from SharePoint (in PQ, I connect to SP, then combine all files) then load to Excel where I can add two columns and manually edit

Plus, another mapping table where it fetches all auto categories