Allocating based on a different table

Hi Anonymous, 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