Forum Discussion

Einomi's avatar
Einomi
Helper V
1 year ago

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's avatar
Nasif_Azam
Super User
1 year ago

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's avatar
    Einomi
    Helper V
    1 year ago

    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

    • Nasif_Azam's avatar
      Nasif_Azam
      Super User
      1 year ago

      Hey Einomi ,

      You want to:

      • Load your transaction table (CSV or Excel),

      • Map Categories/Subcategories automatically from a mapping table,

      • Retain any manual edits you make in Excel (so they're not lost after refresh),

      • Combine both auto and manual in a single output for reporting,

      • Avoid repeated merges and improve performance using Table.Buffer.

      Full M Code From Scratch

      let
    // STEP 1: Load the mapping table from Excel
    MappingTable = Excel.CurrentWorkbook(){[Name="tblMapping"]}[Content],
    MappingBuffered = Table.Buffer(MappingTable),

    // STEP 2: Load transactions from Excel (this is the table with editable Category/Subcategory)
    TransactionsRaw = Excel.CurrentWorkbook(){[Name="tblTransactions"]}[Content],
    TransactionsBuffered = Table.Buffer(TransactionsRaw),

    // STEP 3: Add auto Category/Subcategory using Mapping Table
    AddAutoColumns = Table.AddColumns(
        TransactionsBuffered,
        {
            "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 4: Final logic – Manual input takes priority over auto
    AddFinalCategory = Table.AddColumn(
        AddAutoColumns,
        "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 5: Keep necessary columns
    FinalOutput = Table.SelectColumns(
        AddFinalSubcategory,
        {"Date", "Counter Party", "Reference", "Amount", "FinalCategory", "FinalSubcategory"}
    )

in
    FinalOutput

      Things to remember:

      • The query does not overwrite manual values; any edits you make directly in the tblTransactions table will be retained.

      • The "tblMapping" and "tblTransactions" should be named Excel tables in your workbook.

       

      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's avatar
        Einomi
        Helper V
        1 year ago

        Hi Nasif,

        Thanks for your time, however I strongly suspect your code is generated by an AI, the layout is very similar and I am not sure you addresses my requirements.

        Table.AddColumns does not exist in Power Query, only Table.AddColumn
        Did you try your code ?
        Please review all my messages and feel free to offer a suggestion 🙂