Forum Discussion
Expand rows based on column conditions using power query.
Dear All,
I am trying to remodel a orignal table to achieve desired result. I have tried different ways but without success.
Please advise it would be great help.
Every row defines specific product. Here the age is calculated based on Column
"Current Year" - "Construction Year"
In the desired table I want to return all rows of all the years from the "Construction Year" till "Current Year"
and return "Age by year" which can be recalculated based on "Year" - "Current Year"
Orignal Table
| Product | Type | Construction year | Age | Current Year |
| a | car | 2021 | 1 | 2022 |
| b | truck | 2020 | 2 | 2022 |
| c | jeep | 2019 | 3 | 2022 |
Desired Output
| Product | Type | year | Age by year | Current Year |
| a | car | 2021 | 1 | 2022 |
| a | car | 2022 | 2 | 2022 |
| b | truck | 2020 | 1 | 2022 |
| b | truck | 2021 | 2 | 2022 |
| b | truck | 2022 | 3 | 2022 |
| c | jeep | 2019 | 1 | 2022 |
| c | jeep | 2020 | 2 | 2022 |
| c | jeep | 2021 | 3 | 2022 |
| c | jeep | 2022 | 4 | 2022 |
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 remOthColsSummary:
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
2 Replies
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 remOthColsSummary:
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