Forum Discussion
AFra
Helper III
Helper IIITable transformation : columns become lines
Hi there, I have a 50.000 lines table with 23 columns such as : ID ability A ability B ability C ability D 12345 1 1 12346 1 1 1 1 12347 1 I'd lik...
AFra,
In Power Query, click on the ID column, and select Transform --> Unpivot Other Columns. Then rename the columns as desired.
Fowmy
Super User
Super UserAFra
Paste the below code in a blank query in Power Query and follow the steps
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEyBGIFOB2rA5Exg4ogMEzGHEU9WE8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"ability A" = _t, #"ability B" = _t, #"ability C " = _t, #"ability D" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
#"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Trim, type text}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Trimmed Text", {{"Attribute", each Text.End(_, 1), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Last Characters",{{"Attribute", "Ability"}, {"Value", "Avilable"}})
in
#"Renamed Columns"
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂