Forum Discussion
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 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 |
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 renameShaiksamar2244 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.
13 Replies
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 renameHello 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".
Hello, I get the error message with your code.
"Expression.Error: The name "Rename" was not recognized. Is it spelled correctly?"
