Stack Multiple Columns

[Anonymous]

I have a terrible spreadsheet I can't change the format of . . . .  It's multiple serial numbers across 4 different columns with their individual statuses. 

How can I stack all these columns into one set so I can pivot the data after the back?  

it goes from Serial Number 1 through 50, then S/2 Column starts at 51 and goes to 100.  Each is followed by a status column . . .

[HotChilli · Accepted solution]

You can make 4 versions(copies) of the table.

Remove the appropriate columns so that each version has s/n and status for a pair of columns (1, or 2 or 4 or 8).

Rename the columns in each version to s/n and status. (they must be the same in each version)

Append the tables.

[Anonymous]

This makes a lot of sense; I didn't think logically about it - I can also teach this to someone who doesn't know Power Query.

[wdx223_Daniel]

NewStep=#table({"S/N","Status"},List.TransformMany(List.Split(Table.ToColumns(PreviousStepName),2),List.Zip,(x,y)=>y))