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 

[AlienSx]

@Einomi this is standard self-referencing query stuff. But you must have some unique reference for every line of your statement (or date column will be enough if you load transactions for the whole day). I added index column but just for demo. In short: you have some table with "old" transactions in Excel (with auto and/or manual entries) - load it to PQ, load your source data, this way or another filter new transactions only (this is job for your unique identifier), apply your transformations (including auto category and subcategory stuff), combine old and new transactions and load resulting table back to Excel. Now you may manually edit that table and won't loose this info unless you manually delete rows from your table. 

let
    auto = Function.Invoke(
        Record.FromList,
        List.Reverse(
            Table.ToColumns(
                Table.CombineColumnsToRecord(
                    Excel.CurrentWorkbook(){[Name="auto"]}[Content], 
                    "rec", 
                    {"Category", "Subcategory"}
                )
            )
        )
    ),
    loaded_transactions = Table.Buffer(Excel.CurrentWorkbook(){[Name="loaded"]}[Content]),
    loaded_ref = Record.FromList(List.Repeat({false}, Table.RowCount(loaded_transactions)), List.Transform(loaded_transactions[ref], Text.From)),
    new_transactions = Table.AddColumn(
        Table.SelectRows(statement, (x) => Record.FieldOrDefault(loaded_ref, x[ref], true)),
        "cat", 
        (x) => Record.FieldOrDefault(auto, x[Counter Party], [])
    ), 
    xpand = Table.ExpandRecordColumn(new_transactions, "cat", {"Category", "Subcategory"}, null), 
    z = loaded_transactions & xpand
in
    z

[Einomi]

Hi,

Looks we are getting there. I have tried your file. However, the auto categorization does not look like to work.

I have tried to add one row to the mapping table and it did not refresh in the auto query, I even tried to change one subcategories in the mapping table and still did not reflect that change in the auto query.

The manual input where kept, I really need to be able to manual input and if null then the auto category, manual should override auto.

Finally, I am sure this is a bit basic but, I have read online in some specialized blogs that adding an index column may not be the best way to go as it recalculates and can mess up the data, what is your intake on this ?

Appreciate your time and your help

[Nasif_Azam]

Hey @Einomi ,

Thank you for providing the screenshots and context. To help you reconcile automated and manual Category/Subcategory values into one seamless output for reporting while optimizing performance and minimizing merges.

• Uses Table.Buffer to prevent repetitive evaluation,

• Avoids multiple merges,

• Keeps manual inputs intact,

• Prioritizes auto-matching from your mapping table but allows override via manual entries.

Combined Logic: Merge Auto + Manual Inputs

let
    // Step 1: Buffer mapping table to improve performance
    MappingBuffered = Table.Buffer(tblMapping),

    // Step 2: Add Auto Category and Subcategory from mapping table
    AddAutoMappings = Table.AddColumns(
        tblTransactions,
        {
            "AutoCategory", each try Record.Field(Table.SelectRows(MappingBuffered, (r) => Text.Upper(r[Description]) = Text.Upper([Counter Party])){0}, "Category") otherwise null,
            "AutoSubcategory", each try Record.Field(Table.SelectRows(MappingBuffered, (r) => Text.Upper(r[Description]) = Text.Upper([Counter Party])){0}, "Subcategory") otherwise null
        }
    ),

    // Step 3: Merge Manual and Auto columns into Final ones
    AddFinalCategory = Table.AddColumn(AddAutoMappings, "FinalCategory", each if [Category] <> null and [Category] <> "" then [Category] else [AutoCategory]),
    AddFinalSubcategory = Table.AddColumn(AddFinalCategory, "FinalSubcategory", each if [Subcategory] <> null and [Subcategory] <> "" then [Subcategory] else [AutoSubcategory]),

    // Step 4: Keep only relevant columns for reporting
    FinalOutput = Table.SelectColumns(AddFinalSubcategory, {"Date", "Counter Party", "Reference", "Amount", "FinalCategory", "FinalSubcategory"})
in
    FinalOutput

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Einomi]

Hi,

Thanks for your time. Can you help with the full M code ? looks like it missing some code.

And if you can detail some steps, because we have to do self-merge a query if I am not mistaken to be able to input in Excel

Let me know

[Nasif_Azam]

Hey @Einomi ,

I just sending you the steps of M codes. Let's try the full M code with steps and comments.

let
    // STEP 1: Load and buffer your Mapping Table
    MappingTable = Excel.CurrentWorkbook(){[Name="tblMapping"]}[Content],
    MappingBuffered = Table.Buffer(MappingTable),

    // STEP 2: Load your main Transactions Table (with Category/Subcategory editable manually)
    TransactionsOriginal = Excel.CurrentWorkbook(){[Name="tblTransactions"]}[Content],

    // STEP 3: Self-merge to preserve manual inputs (Category/Subcategory)
    TransactionsBuffered = Table.Buffer(TransactionsOriginal),
    PreserveManualInputs = Table.RemoveColumns(
        Table.NestedJoin(TransactionsBuffered, {"Counter Party", "Reference"}, TransactionsBuffered, {"Counter Party", "Reference"}, "Manual", JoinKind.LeftOuter),
        "Manual"
    ),
    AddManualColumns = Table.ExpandTableColumn(
        Table.NestedJoin(TransactionsBuffered, {"Counter Party", "Reference"}, TransactionsBuffered, {"Counter Party", "Reference"}, "Manual", JoinKind.LeftOuter),
        "Manual",
        {"Category", "Subcategory"},
        {"ManualCategory", "ManualSubcategory"}
    ),

    // STEP 4: Add Auto Category/Subcategory from mapping
    AddAutoMappings = Table.AddColumns(
        AddManualColumns,
        {
            "AutoCategory", each try Record.Field(Table.SelectRows(MappingBuffered, (r) => Text.Upper(r[Description]) = Text.Upper([Counter Party])){0}, "Category") otherwise null,
            "AutoSubcategory", each try Record.Field(Table.SelectRows(MappingBuffered, (r) => Text.Upper(r[Description]) = Text.Upper([Counter Party])){0}, "Subcategory") otherwise null
        }
    ),

    // STEP 5: Decide Final Values → Manual wins over Auto
    AddFinalCategory = Table.AddColumn(AddAutoMappings, "FinalCategory", each if [ManualCategory] <> null and [ManualCategory] <> "" then [ManualCategory] else [AutoCategory]),
    AddFinalSubcategory = Table.AddColumn(AddFinalCategory, "FinalSubcategory", each if [ManualSubcategory] <> null and [ManualSubcategory] <> "" then [ManualSubcategory] else [AutoSubcategory]),

    // STEP 6: Output only relevant columns
    FinalOutput = Table.SelectColumns(AddFinalSubcategory, {"Date", "Counter Party", "Reference", "Amount", "FinalCategory", "FinalSubcategory"})

in
    FinalOutput

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Einomi]

Hi

Thanks for your time but I am bit still confused.

Can we write the M code from scratch becasue I do not see the original load of the transactions table.

Thanks