Divide invoice header amount over invoice lines

[BBConsultancy]

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 solution]

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/

[v-junyant-msft · Accepted solution]

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.

[tackytechtom · Accepted solution]

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/