Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
7 | |
7 | |
6 | |
5 |
User | Count |
---|---|
11 | |
11 | |
9 | |
6 | |
6 |