Forum Discussion
Helper IIUnpivot data and combine by year
This seems like it should be simple but can't figure it out.
I need to unpivot my data from this:
| Customer ID | Year 1 Revenue | Year 1 Category | Year 2 Revenue | Year 2 Category | Year 3 Revenue | Year 3 Category |
| 1 | 313468 | C | 2483 | B | 456 | B |
| 2 | 374 | B | 9187 | B | 3417 | B |
| 3 | 428246 | C | 371304 | C | 977 | A |
To this:
| Cutomer ID | Year | Revenue | Customer Category |
| 1 | 1 | 313468 | C |
| 2 | 1 | 374 | B |
| 3 | 1 | 428246 | C |
| 1 | 2 | 2483 | B |
| 2 | 2 | 9187 | B |
| 3 | 2 | 371304 | C |
| 1 | 3 | 456 | B |
| 2 | 3 | 3417 | B |
| 3 | 3 | 977 | A |
I assume there is some intervening step wehre my data would retain the original column headers that I would need to clean up that might look something like this.
| Cutomer ID | Label 1 | Label 2 | Revenue | Revenue Category |
| 1 | Year 1 Revenue | Year 1 Category | 313468 | C |
| 2 | Year 1 Revenue | Year 1 Category | 374 | B |
| 3 | Year 1 Revenue | Year 1 Category | 428246 | C |
| 1 | Year 2 Revenue | Year 2 Category | 2483 | B |
| 2 | Year 2 Revenue | Year 2 Category | 9187 | B |
| 3 | Year 2 Revenue | Year 2 Category | 371304 | C |
| 1 | Year 3 Revenue | Year 3 Category | 456 | B |
| 2 | Year 3 Revenue | Year 3 Category | 3417 | B |
| 3 | Year 3 Revenue | Year 3 Category | 977 | A |
If I can get this far I can cleanup to get the above table. I can't figure out how to pivot into this in way that keeps the years together across one line. Any suggestions on how to unpivot this data to get basically three lines for each customer, one per year, with corresponding revenue and category for that donor and year combination?
You won't get that 'intermediate' table.
Unpivot everything apart from Customer ID.
Split the Attribute column so that you get a column with only 'Revenue' or 'Category' in it.
Pivot that column with the Value as Values. Use "Don't Aggregate"
That should be it.
Let me know.
3 Replies
- HotChilli
Community Champion
You won't get that 'intermediate' table.
Unpivot everything apart from Customer ID.
Split the Attribute column so that you get a column with only 'Revenue' or 'Category' in it.
Pivot that column with the Value as Values. Use "Don't Aggregate"
That should be it.
Let me know.
jeggenWorked perfectly, thank you!
- ThxAlot
Super User
Nothing to do with pivoting,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYyxDcAwCAR3oXYR4GNwmWQMxP5rBEcoxYt7cRBBTIOUFdMLnorAtcZdwTk/yhEk2zP0ZrFbo4Ltt/YlxAWzv6mxHuiybJsXZb4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Year 1 Revenue" = _t, #"Year 1 Category" = _t, #"Year 2 Revenue" = _t, #"Year 2 Category" = _t, #"Year 3 Revenue" = _t, #"Year 3 Category" = _t]), Cols = Table.ToColumns(Source), Yearly = let customer = Cols{0} in List.Transform(List.Split(List.Skip(Cols), 2), each Table.FromColumns({customer} & _, {"Customer","Revenue","Category"})), ToTable = Table.FromColumns({{1 .. List.Count(Yearly)}, Yearly},{"Year","yearly"}), #"Expanded yearly" = Table.ExpandTableColumn(ToTable, "yearly", {"Customer", "Revenue", "Category"}) in #"Expanded yearly"
