Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am at a total loss on how to approach this problem. I have two tables (bill table & bill payments table) including fields shown below:
Bill Table:
Bill# | Item# | Amount |
Bill1 | Item1 | 8 |
Bill1 | Item2 | 12 |
Bill1 | Item3 | 18 |
Bill2 | Item1 | 6 |
Bill2 | Item2 | 14 |
Bill3 | Item1 | 20 |
Bill4 | Item1 | 30 |
and
Bill Payment Table
Bill Payment # | Bill # | Paid Amount |
BillPmnt1 | Bill2 | 20 |
BillPmnt1 | Bill1 | 19 |
BillPmnt2 | Bill3 | 10 |
BillPmnt3 | Bill4 | 30 |
I need to figure out a way to allocate the paid amount across the items when a partial payment has been made. For example, Bill1 has 3 items costing $8, $12, and $18 for a total of $38. BillPmnt1 includes a $19 payment on Bill1. So, I need the results table to allocate how much was paid at the item level (ex. Item1 Paid Amount = $8 * $19/$38 = $4).
Desired Result
Bill Payment # | Bill # | Item # | Paid Amount |
BillPmnt1 | Bill2 | Item1 | 6 |
BillPmnt1 | Bill2 | Item2 | 14 |
BillPmnt1 | Bill1 | Item1 | 4 |
BillPmnt1 | Bill1 | Item2 | 6 |
BillPmnt1 | Bill1 | Item3 | 9 |
BillPmnt2 | Bill3 | Item1 | 10 |
BillPmnt3 | Bill4 | Item1 | 30 |
First, is this even possible in Power Query? If so, how would I approach this? I thought maybe I'd need to first group the Bill table by Bill # to figure out how much was paid, but I don't really know how I'd proceed from there. Any help is greatly appreciated!!
Thanks!
Solved! Go to Solution.
Hi @JMikes, try this. You have to replace selected code for both tables with your table references:
Result
let
BillTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyTFU0lHyLEnNBdEWSrE6qKJGQNrQCEPYGCSMUG2EZIYZhijYDBO4sDGSYiMDuLAJkrAxUDgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Bill#" = _t, #"Item#" = _t, Amount = _t]),
ChangedTypeBillTable = Table.TransformColumnTypes(BillTable,{{"Amount", Currency.Type}}),
BillPaymentTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyQnIzSsxVNIBs42AtJGBUqwOphSINrREkTKCShmDpFB1GUOlTIC0MVAqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Bill Payment #" = _t, #"Bill #" = _t, #"Paid Amount" = _t]),
ChangedTypeBillPaymentTable = Table.TransformColumnTypes(BillPaymentTable,{{"Paid Amount", Currency.Type}}),
GroupedRowsBillTable = Table.Group(ChangedTypeBillTable, {"Bill#"}, {{"All", each _, type table}, {"Total Amount", each List.Sum([Amount]), Currency.Type} }),
ExpandedAll = Table.ExpandTableColumn(GroupedRowsBillTable, "All", {"Item#", "Amount"}, {"Item#", "Amount"}),
MergedQueryItself = Table.NestedJoin(ChangedTypeBillPaymentTable, {"Bill #"}, ExpandedAll, {"Bill#"}, "Bill Table", JoinKind.LeftOuter),
RenamedColumns = Table.RenameColumns(MergedQueryItself,{{"Paid Amount", "Paid Amount Bill"}}),
ExpandedBillTable = Table.ExpandTableColumn(RenamedColumns, "Bill Table", {"Item#", "Amount", "Total Amount"}, {"Item#", "Amount", "Total Amount"}),
Ad_PaidAmount = Table.AddColumn(ExpandedBillTable, "Paid Amount", each [Amount] * [Paid Amount Bill] / [Total Amount], Currency.Type),
RemovedOtherColumns = Table.SelectColumns(Ad_PaidAmount,{"Bill Payment #", "Bill #", "Item#", "Paid Amount"})
in
RemovedOtherColumns
Hi @JMikes, try this. You have to replace selected code for both tables with your table references:
Result
let
BillTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyTFU0lHyLEnNBdEWSrE6qKJGQNrQCEPYGCSMUG2EZIYZhijYDBO4sDGSYiMDuLAJkrAxUDgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Bill#" = _t, #"Item#" = _t, Amount = _t]),
ChangedTypeBillTable = Table.TransformColumnTypes(BillTable,{{"Amount", Currency.Type}}),
BillPaymentTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyQnIzSsxVNIBs42AtJGBUqwOphSINrREkTKCShmDpFB1GUOlTIC0MVAqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Bill Payment #" = _t, #"Bill #" = _t, #"Paid Amount" = _t]),
ChangedTypeBillPaymentTable = Table.TransformColumnTypes(BillPaymentTable,{{"Paid Amount", Currency.Type}}),
GroupedRowsBillTable = Table.Group(ChangedTypeBillTable, {"Bill#"}, {{"All", each _, type table}, {"Total Amount", each List.Sum([Amount]), Currency.Type} }),
ExpandedAll = Table.ExpandTableColumn(GroupedRowsBillTable, "All", {"Item#", "Amount"}, {"Item#", "Amount"}),
MergedQueryItself = Table.NestedJoin(ChangedTypeBillPaymentTable, {"Bill #"}, ExpandedAll, {"Bill#"}, "Bill Table", JoinKind.LeftOuter),
RenamedColumns = Table.RenameColumns(MergedQueryItself,{{"Paid Amount", "Paid Amount Bill"}}),
ExpandedBillTable = Table.ExpandTableColumn(RenamedColumns, "Bill Table", {"Item#", "Amount", "Total Amount"}, {"Item#", "Amount", "Total Amount"}),
Ad_PaidAmount = Table.AddColumn(ExpandedBillTable, "Paid Amount", each [Amount] * [Paid Amount Bill] / [Total Amount], Currency.Type),
RemovedOtherColumns = Table.SelectColumns(Ad_PaidAmount,{"Bill Payment #", "Bill #", "Item#", "Paid Amount"})
in
RemovedOtherColumns
I need to figure out a way to allocate the paid amount across the items when a partial payment has been made
While this is possible in Power Query it must be stated very strongly that Power BI is a reporting tool, not a resource allocation tool. Power BI has no memory and no concept of variables. You are better off using a specialized tool.
Thanks, @lbendlin . The results will actually be used in Excel. I looked up the Power Query forum and it brought me here. Can you still help?
The results will actually be used in Excel
Then do the computations in Excel too.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.