Forum Discussion
Splitting interleaved column into multiple columns
Hello everyone, I have been using Power Query for a few months now and I have a question for a data clean-up problem that comes up quite often. I am wondering whether there is something in-built in Power Query to handle this quickly.
Basically, a table of data retrieved from certain websites might end up as a single column of data with the columns interleaved when pasted or saved. For example, given a table with columns A, B, and C, the data becomes a single column of:
A data row 1
B data row 1
C data row 1
A data row 2
B data row 2
C data row 2
etc.
An approach to solve this is to index the column, calculate the modulus, filter the modulus to get separate columns, and then append them together. Not hard to do but somewhat tedious for a common operation.
Another is to index the column, calculate the modulus, calculate the integer-divided column, and pivot the table using the modulus as the column headings and the data as values.
Is there a function in Power Query that could do this "de-interleaving" at one go?
2 Replies
Try Unpiviot
It results in the Unpivoted columns step below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYvJDQAgDMN2ybsPWo7CLFX3X4MGPpFjyREwhcBajR6kBHSTnbO+mOTB6V8YmZ22J04hK39vFTEZyLw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value") in #"Unpivoted Columns"