Forum Discussion
roshantellis
4 months agoNew Member
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...
- 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:
v-karpurapud
4 months agoCommunity Support
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.