Forum Discussion
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 like to transform this table so that I only have 3 columns : ID,ability, Available. The final result shoud be :
| ID | Ability | Available |
| 12345 | A | 1 |
| 12345 | B | |
| 12345 | C | 1 |
| 12345 | D | |
| 12346 | A | 1 |
| 12346 | B | 1 |
| 12346 | C | 1 |
| 12346 | D | 1 |
| 12347 | A | |
| 12347 | B | 1 |
| 12347 | C | |
| 12347 | D |
Is there a way to do this easily in powerbi? How? : )
Thanks in advance,
Ana
AFra,
In Power Query, click on the ID column, and select Transform --> Unpivot Other Columns. Then rename the columns as desired.
2 Replies
- DataInsights
Super User
AFra,
In Power Query, click on the ID column, and select Transform --> Unpivot Other Columns. Then rename the columns as desired.
- Fowmy
AFra
Paste the below code in a blank query in Power Query and follow the stepslet 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 🙂
