Expand rows based on column conditions using power query.

Hi Anonymous ,

Paste this example code into Advanced Editor in a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpOLAKSRgZGhkDKEMI0UorViVZKAnJKikqTsyGCBiAKWT4ZyMlKTS0AixlaAiljuHQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Type = _t, #"Construction year" = _t, Age = _t, #"Current Year" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Type", type text}, {"Construction year", Int64.Type}, {"Age", Int64.Type}, {"Current Year", Int64.Type}}),
    addYearList = Table.AddColumn(chgTypes, "yearList", each {[Construction year]..[Current Year]}),
    expandYearList = Table.ExpandListColumn(addYearList, "yearList"),
    addAgeByYear = Table.AddColumn(expandYearList, "Age by Year", each ([yearList] - [Construction year]) + 1),
    remOthCols = Table.SelectColumns(addAgeByYear,{"Product", "Type", "yearList", "Age by Year"})
in
    remOthCols

Summary:

addYearList = Ceate a list of values between construction yer and current year

expandYearList = Expand list to duplicate rows for each year

addAgeByYear = Get the age as at the end of each value of [yearList]

Example Output:

Pete