How to transform row to columns in a new table in Power BI

[Anonymous]

Hi a try some times made that in Power bi with Transpose and pivot but i cannot obtain the correct result.
This is the original data

Identificacion	Materia	NroMatricula	CalifFinal	Carrera	Nivelacion
089032185	FISICA	1	3.5	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
089032185	FUNDAMENTOS DE MATEMATICA	1	5.6	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
089032185	FUNDAMENTOS DE QUIMICA	1	6.6	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
089032185	GEOMETRIA Y TRIGONOMETRIA	1	24.2	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
089032185	LENGUAJE Y COMUNICACION	1	24	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
103582771	FISICA	2	30.4	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
103582771	FUNDAMENTOS DE QUIMICA	2	31.4	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
103582771	GEOMETRIA Y TRIGONOMETRIA	2	31.2	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
103582772	LENGUAJE Y COMUNICACION	2	18	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A
103582771	FUNDAMENTOS DE MATEMATICA	2	30	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A

Identificacion	NroMatricula	Carrera	Nivelacion	FISICA	FUNDAMENTOS DE MATEMATICA	FUNDAMENTOS DE QUIMICA	GEOMETRIA Y TRIGONOMETRIA	LENGUAJE Y COMUNICACION
089032185	1	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A	3.5	5.6	6.6.	24.2	24
103582771	2	INGENIERIA, CIENCIAS Y CIENCIAS ADMINISTRATIVAS	2019-A	30.4	30	31.4	31.2	18

How to do that?, thanks a lot by the help

Roberth

[Anonymous]

Thanks by the tip, I will be testing this evening
By the moment I resolved the problem using Pivot Column Transform in Power Query Editor

The result

Thanks a lot

[v-eachen-msft]

Hi @Anonymous ,

I found that you put "103582772" into the records of "103582771". Do you mean to use the "NroMatricula" to group your data? You could following the following codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZPBDoIwDIZfZeGMZBuieGygkhrXRTdMDPH9X8OiKFGjF/DQrcvS/0v7b12X6HKjc2vKQimVpMmWAlUgiZHIs0JW4gaZ8EiQqoqQK4KgzmMKtSOmEI8Q6QRBKqw2mwUkl/RTvuUaHHL0QdWoHESUGIlFtvov8dCSG3Gr2XENeodRpERAtsbzcB6IdpnZmZF75KaFHfb13rUs7VXk+QmcijM6L0q7Xpu3F9L3ketsdv1vht14ZnbeL8cG5GTHHkj7067+1pT/HefLj7s7OAl4uQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Identificacion = _t, Materia = _t, NroMatricula = _t, CalifFinal = _t, Carrera = _t, Nivelacion = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Identificacion", Int64.Type}, {"Materia", type text}, {"NroMatricula", Int64.Type}, {"CalifFinal", type number}, {"Carrera", type text}, {"Nivelacion", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"NroMatricula"}, {{"Identificacion", each List.Min([Identificacion]), type number}, {"Carrera", each _, type table [Identificacion=number, Materia=text, NroMatricula=number, CalifFinal=number, Carrera=text, Nivelacion=text]}}),
    #"Expanded Carrera" = Table.ExpandTableColumn(#"Grouped Rows", "Carrera", {"Materia", "CalifFinal", "Carrera", "Nivelacion"}, {"Carrera.Materia", "Carrera.CalifFinal", "Carrera.Carrera", "Carrera.Nivelacion"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Carrera", List.Distinct(#"Expanded Carrera"[Carrera.Materia]), "Carrera.Materia", "Carrera.CalifFinal")
in
    #"Pivoted Column"

[Greg_Deckler]

@ImkeF 

[ImkeF · Accepted solution]

Hi @Anonymous 

assuming there is a little typo on the row before the last, you can retrieve the result by pivoting on "Materia" with column "Califfinal" in the values. Make sure to choose "Don't aggregate" in the aggregation options:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdNLDoIwEAbgqxDWSNoiissJjGSMnUZbTIzxbp7FkzkokRgfG2xIX4vOl/Yvp1OqqpUqjK7K66X/0ixdk6caZKKlFXkpPXGLTLgnyJKakGsCnxzHKTSWmHzYQ6ADeNlhlF7NID1nH4WOG7DIwfmkwcRCQGkjWuaL6OiuIzuKixhii85ikGpSQ4bW8bAeUDPPzf/VLXLbwQb7Es52LIesyfHTnCpqVZSVWS71+4PpT1OoPAbxLbw7qWOQv9Ib1Mnpvavf0+sxXUW/2pef8RHoJPN8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Identificacion = _t, Materia = _t, NroMatricula = _t, CalifFinal = _t, Carrera = _t, Nivelacion = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Identificacion", Int64.Type}, {"Materia", type text}, {"NroMatricula", Int64.Type}, {"CalifFinal", type number}, {"Carrera", type text}, {"Nivelacion", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Materia]), "Materia", "CalifFinal")
in
#"Pivoted Column"