Adding Conditional Rows to a Table by Line Item

[AnotherBIUser]

Hello,

I am looking to model out some revenue. In order to do so I need to convert a single line item into multiple rows listing out the billing period. For example, 

Example of what multiple line items would look like:

I have been looking into how to do this with the advanced editor in powerquery, but found no direct comparison in the current forms. I would appreciate any help that anyone can provide! 

Thanks,

[Anonymous]

Hi @AnotherBIUser ,

Thanks for the reply of AlienSx , Omid_Motamedise and lbendlin!
And @AnotherBIUser , after testing, their replies are very effective. If these replies can help you solve your problem, please don't forget to accept their replies as solutions so that other users can find solutions faster! Thanks!

Best Regards,
Dino Tao

[AlienSx]

Table.FromList(
    List.TransformMany(
        Table.ToRows(Source), 
        (x) => List.Zip(
            {
                List.Generate(() => 0, (w) => w < x{2}, (w) => w + 1, (w) => Date.AddMonths(x{0}, w)), 
                List.Numbers(x{2}, x{2}, -1)
            }
        ),
        (x, y) => {y{0}, x{1}, y{1}}
    ), 
    (x) => x,
    Value.Type(Source)
)

[Omid_Motamedise]

use this formula based on List.Transform

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJR0lEyNDAwAFFGSrE60UrGSBKmYAkgOzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Revenue Start Date" = _t, #"Revenue Per Month" = _t, #"Contract Length (mo)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract Length (mo)", Int64.Type}, {"Revenue Per Month", Int64.Type}, {"Revenue Start Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform({0..[#"Contract Length (mo)"]}, (x)=> [new Date=Date.AddMonths([Revenue Start Date],x), New length=[#"Contract Length (mo)"]-x])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"new Date", "New length"}, {"new Date", "New length"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Revenue Start Date", "Contract Length (mo)"})

    in
    #"Removed Columns"

[lbendlin · Accepted solution]

Contract Length says 12 months yet you have 13 months of amortized revenue?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJR0lEyNDAwAFFGSrE60UrGSBKmYAkgOzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Revenue Start Date" = _t, #"Revenue Per Month" = _t, #"Contract Length (mo)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Revenue Start Date", type date}, {"Revenue Per Month", Currency.Type}, {"Contract Length (mo)", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0..[#"Contract Length (mo)"]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",each [Revenue Start Date],each Date.AddMonths([Revenue Start Date],[Custom]),Replacer.ReplaceValue,{"Revenue Start Date"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [#"Contract Length (mo)"],each [#"Contract Length (mo)"]-[Custom],Replacer.ReplaceValue,{"Contract Length (mo)"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Custom"})
in
    #"Removed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[AnotherBIUser]

Hi! 

Thanks so much for your response. I've been looking to test this with my data, but am coming across the issue of two "let" functions requiring a token identifier? When using your direct code I see no issue, but when replacing the source with a reference to the premade table I receive this error: 

Apologies if I'm missing something simple. I have not used two "let" functions in any of my queries. 

Thanks!

[lbendlin]

that's a leftover of my sample code that you were supposed to remove   (meta data to describe the column types of the sample data)

remove everything in that line starting at the squiggely.

[AnotherBIUser]

That worked, thank you. One more follow up. You are correct pointing out my example has 13mo of amortized revenue and that it is incorrect. Is there an edit where the 0 month will not populate?

[lbendlin]

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..[#"Contract Length (mo)"]}),

change 0 to 1