Forum Discussion
Solved
Add rows to a table based on cell value
| MajorGroup | MinorGroup | Sample |
| 1 | 6 | 3 |
| 1 | 5 | 2 |
Above is an example of my data table to which I am trying to add the # of samples for each MajorGroup/MinorGroup as rows counting down to 1. An example of the desired output table is seen below.
---->
| MajorGroup | MinorGroup | Sample |
| 1 | 6 | 3 |
| 1 | 6 | 2 |
| 1 | 6 | 1 |
| 1 | 5 | 2 |
| 1 | 5 | 1 |
You can transform the Sample column (or add a new column) that creates a list {1,2,..,N} and then expand that list column to new rows.
Here's the full query I used:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIDYmOlWB0IzxSIjZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MajorGroup = _t, MinorGroup = _t, Sample = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MajorGroup", Int64.Type}, {"MinorGroup", Int64.Type}, {"Sample", Int64.Type}}), #"Number to list" = Table.TransformColumns(#"Changed Type", {{"Sample", each List.Reverse({1.._}), type list}}), #"Expanded Sample" = Table.ExpandListColumn(#"Number to list", "Sample") in #"Expanded Sample"
2 Replies
You can transform the Sample column (or add a new column) that creates a list {1,2,..,N} and then expand that list column to new rows.
Here's the full query I used:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIDYmOlWB0IzxSIjZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MajorGroup = _t, MinorGroup = _t, Sample = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MajorGroup", Int64.Type}, {"MinorGroup", Int64.Type}, {"Sample", Int64.Type}}), #"Number to list" = Table.TransformColumns(#"Changed Type", {{"Sample", each List.Reverse({1.._}), type list}}), #"Expanded Sample" = Table.ExpandListColumn(#"Number to list", "Sample") in #"Expanded Sample"Thanks so much for the help! This worked perfectly. 👍
