Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Shaiksamar2244
Frequent Visitor

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 would be appreciated.

 

Document#Bill DateTerm Start DateTerm End DateBilling FrequencyAmount
1237/31/20236/30/20219/30/2023Monthly200
4568/27/20231/1/20213/31/2024Quarterly200
7899/17/202311/1/202112/30/2024Annual200
9878/15/20239/29/20229/28/2024Semi-Annual200

 

Output

Document#Bill DateTerm Start DateTerm End DateBilling FrequencyAmount
1237/31/20236/30/20219/30/2023Monthly200
1238/31/20236/30/20219/30/2023Monthly200
1239/30/20236/30/20219/30/2023Monthly200
4568/27/20231/1/20213/31/2024Quarterly200
45611/27/20231/1/20213/31/2024Quarterly200
4562/27/20241/1/20213/31/2024Quarterly200
7899/17/202311/1/202112/30/2024Annual200
7899/17/202411/1/202112/30/2024Annual200
9878/15/20239/29/20229/28/2024Semi-Annual200
9872/15/20249/29/20229/28/2024Semi-Annual200
9878/15/20249/29/20229/28/2024Semi-Annual200
2 ACCEPTED SOLUTIONS
AlienSx
Super User
Super User

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

 

 

View solution in original post

@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. 

View solution in original post

13 REPLIES 13
AlienSx
Super User
Super User

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

 

 

Hello, I get the error message with your code.

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

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

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

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.

That's how it worked, thanks for the quick help. 😉

Hello Alien, 

 

Thank you so much for your response. I'm new to this M code, When I use this same code in a power query to understand how this works, I got this error. Please assist me.

Shaiksamar2244_0-1690012923239.png

 

 

In step named tbl replace word Source with #"Changed Type"

Another Error

Shaiksamar2244_0-1690024530742.png

 

I told you to change step named tbl. Leave expand step alone, please. It's 18th row in PQ editor. 

Alien, 

 

I got the output, But not sure why I'm getting this error message below the header.

Shaiksamar2244_0-1690026873834.png

 

@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. 

It worked Thank you so much Alien, You are the best 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.