Forum Discussion

Shaiksamar2244's avatar
Shaiksamar2244
Frequent Visitor
3 years ago
Solved

Duplicate rows based on billing frequency

Hello Everyone,   Here is my issue, Based on billing frequency I need duplicate rows to generate for each row until the Term End date. I have shared the output that I'm looking for. Any suggestions...
  • AlienSx's avatar
    3 years ago

    Hello, Shaiksamar2244 

     

     

    let
        Source = your_table,
        f_rec = 
            [Monthly = (x) => Date.AddMonths(x, 1),
            Quarterly = (x) => Date.AddQuarters(x, 1),
            Annual = (x) => Date.AddYears(x, 1),
            #"Semi-Annual" = (x) => Date.AddQuarters(x, 2)],
        f = (r as record) as list =>
            [fx = Record.Field(f_rec, r[Billing Frequency]),
            b = List.Generate(
                    () => r[Bill Date],
                    (x) => x <= r[Term End Date],
                    (x) => fx(x)
            )][b],
        tbl = Table.AddColumn(Source, "lst", f),
        expand = Table.ExpandListColumn(tbl, "lst"),
        remove_old = Table.RemoveColumns(expand,{"Bill Date"}),
        rename = Table.RenameColumns(remove_old,{{"lst", "Bill Date"}})
    in
        rename

     

     

  • AlienSx's avatar
    AlienSx
    3 years ago

    Shaiksamar2244 don't worry. Refresh a query or select some step in the middle and select final step again. Error should go away in PQ editor. I hope so.