Divide invoice header amount over invoice lines

Hi BBConsultancy ,

How about this? 🙂

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYAAlMlHSUDENYzMFaK1UESNQRiVBEjDBFjDBETDBFTDBEzDBFzsEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice Number" = _t, #"Invoice Line Number" = _t, InvoiceDiscount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Number", Int64.Type}, {"Invoice Line Number", Int64.Type}, {"InvoiceDiscount", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Invoice Line Number] = 0)),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice Number"}, {{"MaxLineNumber", each List.Max([Invoice Line Number]), type nullable number}}),
    #"Merged Queries 1" = Table.NestedJoin(#"Filtered Rows", {"Invoice Number"}, #"Grouped Rows", {"Invoice Number"}, "Grouped Rows 1", JoinKind.LeftOuter),
    #"Expanded Grouped Rows 2" = Table.ExpandTableColumn(#"Merged Queries 1", "Grouped Rows 1", {"MaxLineNumber"}, {"MaxLineNumber"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows 2", "SpreadInvoiceDiscountOverLines", each [InvoiceDiscount] / [MaxLineNumber]),
    #"Merged Queries 2" = Table.NestedJoin(#"Changed Type", {"Invoice Number"}, #"Added Custom", {"Invoice Number"}, "Grouped Rows 2", JoinKind.LeftOuter),
    #"Expanded Grouped Rows 1" = Table.ExpandTableColumn(#"Merged Queries 2", "Grouped Rows 2", {"SpreadInvoiceDiscountOverLines"}, {"SpreadInvoiceDiscountOverLines"}),
    #"Replace Values" = Table.ReplaceValue(#"Expanded Grouped Rows 1",each [InvoiceDiscount],each if [InvoiceDiscount] is null then [SpreadInvoiceDiscountOverLines] else [InvoiceDiscount],Replacer.ReplaceValue,{"InvoiceDiscount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replace Values",{"SpreadInvoiceDiscountOverLines"})
in
    #"Removed Columns"

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Hi BBConsultancy ,

tackytechtom Good Answer!
And you can try this:
Here is my sample data:

Duplicate the original form:

And in Table(2):

Then merge Table and Table(2) as a new one and expand:

In the new table Merge1, add a custom column:

if [Table.lineNumber] = 0 then 0.03 else 0.03/([Count] - 1)

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.