Unpivot data and combine by year

[jeggen]

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?

[ThxAlot]

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"

[jeggen]

Worked perfectly, thank you!

[HotChilli · Accepted solution]

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.