Power query formula for adjusting rounding in columns - Multi input

[roshantellis]

Hi All

https://learn.microsoft.com/en-us/answers/questions/5841203/power-query-formula-for-adjusting-roundi...

I had a file in which I needed to ensure that the debit side matched with the credit side and the rounding issue was sorted in the attached thread.

Now I have an updated version of this file which takes multiple inputs and gives me multiple outputs

I had applied the mentioned solution to the same file but while my "Debit" and "Credit" sides are matching overall, certain rows are not rounding the "credit" figures with the corresponding "debit".

Each SUM of "credit" must match the "debit" mentioned above it in order to close the voucher in our accounting system otherwise it would lead to an error.

As mentioned in the previous thread, it has to be done by adjusting the first line of "credit". The amount for "debit" should not change.

Also the number of stores will increase with time so the formula must capture the new lines which will be added in the stores table in the future in order to have the sum of both lines to be equal.

The thread I have linked has a screenshot with final code but I can provide any required detail in the thread below.

Kindly support. Thanks.

[v-karpurapud]

Hi @roshantellis 

Following up since we haven't heard back regarding our previous message. Have you had a chance to review the information above? Please let us know if you have any further questions.

Regards,

Microsoft Fabric Community Support Team.

[v-karpurapud]

Hi @roshantellis 

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @lbendlin and @ronrsnfld   for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution?If you have any more questions, please let us know and we’ll be happy to help.

Regards,

Microsoft Fabric Community Support Team.

[ronrsnfld · Accepted solution]

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:

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...