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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors