Duplicate rows based on billing frequency

[Shaiksamar2244]

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 Date	Term Start Date	Term End Date	Billing Frequency	Amount
123	7/31/2023	6/30/2021	9/30/2023	Monthly	200
456	8/27/2023	1/1/2021	3/31/2024	Quarterly	200
789	9/17/2023	11/1/2021	12/30/2024	Annual	200
987	8/15/2023	9/29/2022	9/28/2024	Semi-Annual	200

Output

Document#	Bill Date	Term Start Date	Term End Date	Billing Frequency	Amount
123	7/31/2023	6/30/2021	9/30/2023	Monthly	200
123	8/31/2023	6/30/2021	9/30/2023	Monthly	200
123	9/30/2023	6/30/2021	9/30/2023	Monthly	200
456	8/27/2023	1/1/2021	3/31/2024	Quarterly	200
456	11/27/2023	1/1/2021	3/31/2024	Quarterly	200
456	2/27/2024	1/1/2021	3/31/2024	Quarterly	200
789	9/17/2023	11/1/2021	12/30/2024	Annual	200
789	9/17/2024	11/1/2021	12/30/2024	Annual	200
987	8/15/2023	9/29/2022	9/28/2024	Semi-Annual	200
987	2/15/2024	9/29/2022	9/28/2024	Semi-Annual	200
987	8/15/2024	9/29/2022	9/28/2024	Semi-Annual	200

[AlienSx · Accepted solution]

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 · Accepted solution]

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]

Hello, I get the error message with your code.

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

[AlienSx]

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

[hegler23]

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]

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.

[hegler23]

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

[Shaiksamar2244]

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.

[AlienSx]

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

[Shaiksamar2244]

Another Error

[AlienSx]

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

[Shaiksamar2244]

Alien, 

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

[AlienSx · Accepted solution]

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

[Shaiksamar2244]

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