Forum Discussion

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 🙂

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    1 year ago

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

      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