Divide invoice header amount over invoice lines

Question

I have a data table with invoice lines. Invoice headers are also in the table and are marked with invoice line number 0. The invoice discount is marked on the invoice header and I want to take the invoice header discount and divide it over the lines of the invoice.

What I want to achieve in Power Query is the following:

If lineNumber is not 0 then take the InvoiceDiscount of the header of this invoice (line number 0) and divide it by the total amount of lines for this invoice.

So in the example in thepicture below I want to replace all null with 0,03/7=

Looking forward to your help!

tackytechtom · Accepted Answer

Hi&nbsp;BBConsultancy&nbsp;,
&nbsp;
How about this? 🙂
&nbsp;
Before:

&nbsp;
After:

&nbsp;
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"
&nbsp;
Let me know, if this helps 🙂
&nbsp;
/Tomhttps://www.tackytech.blog/https://www.instagram.com/tackytechtom/