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...
Need Help
  • AlienSx's avatar
    AlienSx
    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. 

AlienSx's avatar
AlienSx
Super User
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

 

 

hegler23's avatar
hegler23
Frequent Visitor
3 years ago

Hello, I get the error message with your code.

"Expression.Error: The name "Rename" was not recognized. Is it spelled correctly?"

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hello, hegler23 Power Query M is case sensitive so try rename instead. Or show the code you use to locate a problem.

    • hegler23's avatar
      hegler23
      Frequent Visitor
      3 years ago

      This is my code:

      let
      Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
      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(Quelle, "lst", f),
      expand = Table.ExpandListColumn(tbl, "lst"),
      remove_old = Table.RemoveColumns(expand,{"Bill Date"}),
      umzubenennen = Table.RenameColumns(umzubenennen,{{"lst", "Bill Date"}})
      in
      umzubenennen

      • AlienSx's avatar
        AlienSx
        Super User
        3 years ago

        umzubenennen = Table.RenameColumns(umzubenennen,{{"lst", "Bill Date"}})

        is reffereing to itself. I would try 

        umzubenennen = Table.RenameColumns(remove_old,{{"lst", "Bill Date"}})

        instead. 

        P.S. I've corrected the same error in my original code. Sorry for confusion.