Forum Discussion
cottrera
5 years agoPost Prodigy
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 ...
- Anonymous5 years ago
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"
Anonymous
5 years agoNot applicable
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"