Forum Discussion
Power Query - Duplicate rows and add additional columns
Hi
I have a table with one row per address
| Address & Income Table | ||
| Property Reference | Address | Income |
| 1234 | 100 Dax Street | £3,000 |
| 5678 | 303 Calculate Road | £4,000 |
| 91011 | 123 Evaluate Street | £2,500 |
I would like to use power query to duplicate each of these rows multiple times but also add some additional columns (shown below) On the actual report I would like 30 rows adding per property but for this dummy example I am just show you 6 rows
In addition to this request I would like the first date to have the current year and all remaining dates there after to change based on the first date
| Columns to add | ||
| Date | Order | Discount Rate |
| 01/01/2021 | 1 | 0.037 |
| 01/01/2022 | 2 | 0.037 |
| 01/01/2023 | 3 | 0.037 |
| 01/01/2024 | 4 | 0.037 |
| 01/01/2025 | 5 | 0.037 |
| 01/01/2026 | 6 | 0.027 |
The reason for this request is for my companies 30 year plan , which I will be adding Net Present Value (NPV)
My expected outcome based on my example table would be ,
| Desired Outcome | ||||
| Property Reference | Address | Income | index | Date |
| 1234 | 100 Dax Street | £3,000 | 1 | 01/01/2021 |
| 1234 | 100 Dax Street | £3,000 | 2 | 01/01/2022 |
| 1234 | 100 Dax Street | £3,000 | 3 | 01/01/2023 |
| 1234 | 100 Dax Street | £3,000 | 4 | 01/01/2024 |
| 1234 | 100 Dax Street | £3,000 | 5 | 01/01/2025 |
| 1234 | 100 Dax Street | £3,000 | 6 | 01/01/2026 |
| 5678 | 303 Calculate Road | £4,000 | 1 | 01/01/2021 |
| 5678 | 303 Calculate Road | £4,000 | 2 | 01/01/2022 |
| 5678 | 303 Calculate Road | £4,000 | 3 | 01/01/2023 |
| 5678 | 303 Calculate Road | £4,000 | 4 | 01/01/2024 |
| 5678 | 303 Calculate Road | £4,000 | 5 | 01/01/2025 |
| 5678 | 303 Calculate Road | £4,000 | 6 | 01/01/2026 |
| 91011 | 123 Evaluate Street | £2,500 | 1 | 01/01/2021 |
| 91011 | 123 Evaluate Street | £2,500 | 2 | 01/01/2022 |
| 91011 | 123 Evaluate Street | £2,500 | 3 | 01/01/2023 |
| 91011 | 123 Evaluate Street | £2,500 | 4 | 01/01/2024 |
| 91011 | 123 Evaluate Street | £2,500 | 5 | 01/01/2025 |
| 91011 | 123 Evaluate Street | £2,500 | 6 | 01/01/2026 |
Thank you
Richard
Hi cottrera
You said 30 rows, the sample was 6 rows. And I am not sure if you have that table contains Date, Order, Discount Rate, so I am doing it with 6 rows and no other table. If you need 30 rows, change Counter =30
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTI0MFBwSaxQCC4pSk0tAQocWmysY2BgoBSrE61kamZuARQyNjBWcE7MSS7NSSxJVQjKT0wBqzOBq7M0NDA0BBlmZKzgWpaYUwpSh2SikY4pSGUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Reference" = _t, Address = _t, Income = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property Reference", Int64.Type}, {"Address", type text}, {"Income", Currency.Type}}), Counter=6, initialList = {0..Counter-1}, startYear = Date.Year( DateTime.LocalNow()), YearList = List.Transform(initialList, each _ +startYear), #"Converted to Table" = Table.FromList(YearList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Date", each #date([Column1],1,1)), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Column1"}), Custom1 = Table.AddColumn(#"Changed Type", "new",each #"Removed Columns"), #"Expanded new" = Table.ExpandTableColumn(Custom1, "new", {"Date", "Index"}, {"Date", "Index"}) in #"Expanded new"
2 Replies
Hi cottrera
You said 30 rows, the sample was 6 rows. And I am not sure if you have that table contains Date, Order, Discount Rate, so I am doing it with 6 rows and no other table. If you need 30 rows, change Counter =30
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTI0MFBwSaxQCC4pSk0tAQocWmysY2BgoBSrE61kamZuARQyNjBWcE7MSS7NSSxJVQjKT0wBqzOBq7M0NDA0BBlmZKzgWpaYUwpSh2SikY4pSGUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Reference" = _t, Address = _t, Income = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property Reference", Int64.Type}, {"Address", type text}, {"Income", Currency.Type}}), Counter=6, initialList = {0..Counter-1}, startYear = Date.Year( DateTime.LocalNow()), YearList = List.Transform(initialList, each _ +startYear), #"Converted to Table" = Table.FromList(YearList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Date", each #date([Column1],1,1)), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Column1"}), Custom1 = Table.AddColumn(#"Changed Type", "new",each #"Removed Columns"), #"Expanded new" = Table.ExpandTableColumn(Custom1, "new", {"Date", "Index"}, {"Date", "Index"}) in #"Expanded new"Thank you Vera_33 your M-Code worked on my demo table. I will attempt to edit it so that it works on my acutual live table
regards
Richard
