Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
5 years ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    5 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"