Unpivot 2 Paired Columns together

[hazwoper]

Hello-- I would like to unpivot each paired Action/Category columns if there is any text in any of the action categories. This is the format of my data:

Account	Region	Manager	Action1	Category1	Action2	Category2	Action3	Category3
ABC	West	John Smith	Sign Document	Office			Empty Bin	Waste
XYZ	North	John Doe			Make Copies	Office
123	South	Jane Doe			Send Email	Office	Recycle boxes	Waste

Not all action columns will have text in it; if they do, then the category column will always be filled in next to it. (IE. a category column will be empty unless there is text in the action category to the left of it). 

I would like to extract each action/category pairing into its own row if there is any text in the action categories, so it is formatted like this:

Account	Region	Manager	Action	Category
ABC	West	John Smith	Sign Document	Office
ABC	West	John Smith	Empty Bin	Waste
XYZ	North	John Doe	Make Copies	Office
123	South	Jane Doe	Send Email	Office
123	South	Jane Doe	Recycle Boxes	Office

I've tried combinations of selecting unpivot columns but nothing is working for me. Any ideas?

[hazwoper]

@AlexisOlson Thank you! It worked. 

[AlexisOlson · Accepted solution]

Unpivot all columns except the first three columns,

Filter blank values and split off the number suffix:

Pivot the attribute column:

Full sample query you can paste into the Advanced Editor of a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7NCsIwDADgVwk976I+gfu5CCrYgz9lh1ozV7Y2w3bg3t627jD0EJIQ8iVCsG1esIyd0fmQdtRa4Eb7NjRcPy2UpEaDNg6PTaMVhgLmqMzgJ8i1jYB0HlmdCXa53kJ/oFdCkljScm0vO4SCBo3uX43Car2J52n8CtLij8DRPqAyUvdL4IRqUj3Cnd5Jnl+qPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Region = _t, Manager = _t, Action1 = _t, Category1 = _t, Action2 = _t, Category2 = _t, Action3 = _t, Category3 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account", "Region", "Manager"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")),
    #"Split Column by Positions" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByPositions({0,1}, true), {"Attribute", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Positions", List.Distinct(#"Split Column by Positions"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"