Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
@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.
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.
In step named tbl replace word Source with #"Changed Type".
Another Error
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 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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
9 | |
7 | |
5 | |
5 | |
5 |
User | Count |
---|---|
10 | |
8 | |
6 | |
6 | |
6 |