Forum Discussion

roshantellis's avatar
roshantellis
New Member
4 months ago
Solved

Power query formula for adjusting rounding in columns - Multi input

Hi All https://learn.microsoft.com/en-us/answers/questions/5841203/power-query-formula-for-adjusting-rounding-in-colu?page=1&orderby=helpful&comment=answer-12696389&translated=false#newest-answer-co...
  • ronrsnfld's avatar
    4 months ago

    From what you show it is not possible to ascertain what you want for a result. But to adjust the Debit amount to be the same as the sum of the credit amounts, with multiple Debit splits in your table, you can use the below code (read the comments in the code to understand the algorithm).

    Sample Data:

     

     

    let
    
    //Change Source to reflect your actual data source
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    /*Using the fourth and fifth arguments of the Table.Group function,
     we can group by each group of Debits*/
        #"Grouped Rows" = Table.Group(#"Source", {"Debit"}, {
            {"all", each _, type table [Debit=nullable number, Credit=nullable number]}},
            GroupKind.Local,(x,y)=>Number.From(x[Debit]<>null and y[Debit]<>null)),
    
    /*Compute the adjustment to the debit
        Note the use of Precision.Decimal in List.Sum
        Then adjust the Debit amount to match the credits*/
        #"Added Adjusted Debit" = Table.AddColumn(#"Grouped Rows","Adjusted Debit", (x)=>
                [a=List.Sum(x[all][Credit], Precision.Decimal),
                 b=x[Debit] - a,
                 c=x[Debit] - b][c], type number),
        
    /*Then subtract the total credits from the adjust debit to produce the zero difference
      Depending on what you want, you can set the data type to either number or Currency
      and either expand or delete the Table column*/
        #"Added Reconciliation" = Table.AddColumn(#"Added Adjusted Debit","Reconciliation", each
             [Adjusted Debit] - List.Sum([all][Credit],Precision.Decimal), Currency.Type)
    in
        #"Added Reconciliation"

     

    Result: