Add custom Row based on Dax logic

Hi Puja ,

Are you trying to create bookkeeping journal entries with debits and credits that balance to zero? If so, I recommend using Power Query to generate double-entry bookkeeping records. DAX is not ideal for this task, but Power Query excels at creating structured double-entry bookkeeping entries. I use it for this purpose regularly.

Here’s how you can do it:

Process in Power Query:

1. Start with your transaction data table.
2. Create a custom column named "Asset Purchases" that calculates the value based on your formula:

("Wages - Temp Staff" + "Payroll Tax Temp") * -1

1. Unpivot the columns so that you have the debits and credits structured in rows.

Below is the Power Query M code for this process:

let
    // Load your transaction data
    Source = Table.FromRows(
        {
            {"ID001", "Wages - Temp Staff", 1000},
            {"ID001", "Payroll Tax Temp", 200},
            {"ID002", "Wages - Temp Staff", 1500},
            {"ID002", "Payroll Tax Temp", 300}
        },
        {"ID", "Type", "Value"}
    ),
    
    // Group the data by ID
    GroupedData = Table.Group(
        Source, 
        {"ID"}, 
        {
            {"AllData", each _, type table [ID=nullable text, Type=nullable text, Value=nullable number]}
        }
    ),

    // Add a custom row for "Asset Purchases" based on the logic
    AddAssetPurchases = Table.TransformColumns(
        GroupedData, 
        {"AllData", each Table.Combine({
            _,
            Table.FromRows(
                {{[ID = List.First(_[ID]), Type = "Asset Purchases", Value = ([Value]{0} + [Value]{1}) * -1]}},
                Table.Type(_)
            )
        })}
    ),

    // Expand the grouped data back to a flat table
    ExpandedData = Table.ExpandTableColumn(AddAssetPurchases, "AllData", {"ID", "Type", "Value"}),

    // Unpivot if required (for debit/credit separation)
    UnpivotedData = Table.UnpivotOtherColumns(ExpandedData, {"ID", "Type"}, "Attribute", "Value")
    
in
    UnpivotedData

This method ensures your bookkeeping entries balance to zero by automatically generating the corresponding "Asset Purchases" entry and formatting the data appropriately. Let me know if you need further assistance!

Best regards,