Table transformation : columns become lines

[AFra]

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 

[Fowmy]

@AFra 

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 🙂

YouTube  LinkedIn

[DataInsights · Accepted solution]

@AFra,

In Power Query, click on the ID column, and select Transform --> Unpivot Other Columns. Then rename the columns as desired.