Power Query - Duplicate rows and add additional columns

[cottrera]

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

[cottrera]

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

[Vera_33 · Accepted solution]

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"