Power query formula for adjusting rounding in 2 columns

After you calculate the credit split for each store, Power Query must correct the small rounding differences. Probably the safest way to do this would be to add the total rounding difference back into the first record. This requires no hard-coding of the number of stores and automatically works when new stores are added. The process is: add an index, calculate the rounding variance, add the variance to the first row only, then remove the helper index.

From the Power Query M formula perspective, this means replacing PrevStepName with the name of the step that produces your rounded Credit column and replacing RawCredit if your unrounded values use a different name.

let
    Source = PrevStepName,

    AddIndex = 
        Table.AddIndexColumn(Source,"AdjIndex",0,1),

    SumRounded = 
        List.Sum(AddIndex[Credit]),

    SumRaw = 
        List.Sum(AddIndex[RawCredit]),

    DebitAmount = 
        SumRaw,

    Variance = 
        DebitAmount - SumRounded,

    AdjustCredit =
        Table.TransformColumns(
            AddIndex,
            {
                {
                    "Credit",
                    each if [AdjIndex]=0 then _ + Variance else _,
                    type number
                }
            }
        ),

    RemoveIndex = 
        Table.RemoveColumns(AdjustCredit,{"AdjIndex"})
in
    RemoveIndex

This way the total of Credit after this step should always equal the Debit amount. The adjustment should work regardless of how many stores exist now or are added later.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin