Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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"
Thanks but this is not correct and is the opposite of what I wanted.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |