Forum Discussion
Unpivot Table but Auto Generate New Rows
Hi,
Can you please help me with the following table manipulation? It would be best if it can be achieved through power query but dax query is also acceptable.
Basically, I have one table with persons' names and their starte_number and end_number values. I want to create rows with numbers that fall into the start_number and end _number range for each person.
Please refer to the tables below:
Raw data:
| Person | Start Number | End Number |
| A | 2 | 6 |
| B | 3 | 5 |
| C | 1 | 4 |
| D | 5 | 8 |
Output:
| Person | Number |
| A | 2 |
| A | 3 |
| A | 4 |
| A | 5 |
| A | 6 |
| B | 3 |
| B | 4 |
| B | 5 |
| C | 1 |
| C | 2 |
| C | 3 |
| C | 4 |
| D | 5 |
| D | 6 |
| D | 7 |
| D | 8 |
Please let me know if anyone has any thoughts on this. Thanks in advance!
Here you go derickyy
This:
becomes this
Here is the full M code, and then I'll explain what I did.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYjOlWJ1oJScgyxiITcE8ZyDLEIhNwDwXsLiOkoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Start Number" = _t, #"End Number" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Number", Int64.Type}, {"End Number", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {[Start Number]..[End Number]}), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Person", "Range"}), #"Expanded Range" = Table.ExpandListColumn(#"Removed Other Columns", "Range") in #"Expanded Range"Ignore the SOURCE row. That is just me pasting in your sample data.
I changed the start/end to integers.
The custom column I added a range using a list. So {1..5} will give me a list of 1, 2, 3, 4, 5. So your start/end numbers looked like this:
Then I only kept the Person and new Range column.
Finally, I expanded the Range to new rows.
Any questions, let me know!
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
1 Reply
Here you go derickyy
This:
becomes this
Here is the full M code, and then I'll explain what I did.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYjOlWJ1oJScgyxiITcE8ZyDLEIhNwDwXsLiOkoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Start Number" = _t, #"End Number" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Number", Int64.Type}, {"End Number", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {[Start Number]..[End Number]}), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Person", "Range"}), #"Expanded Range" = Table.ExpandListColumn(#"Removed Other Columns", "Range") in #"Expanded Range"Ignore the SOURCE row. That is just me pasting in your sample data.
I changed the start/end to integers.
The custom column I added a range using a list. So {1..5} will give me a list of 1, 2, 3, 4, 5. So your start/end numbers looked like this:
Then I only kept the Person and new Range column.
Finally, I expanded the Range to new rows.
Any questions, let me know!
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.