Agregar automaticamente columnas como valores de registro haya en los valores de un campo

[MARRASARG1974]

Hola, tengo la siguiente tabla:

NIVEL	DESCRIPCION	CANTIDAD
1	A	10
2	B	15
3	C	20
1	D	12
2	E	14
3	F	15

Y quisiera que mediante un loop o alguna forma automatica me agregue columnas como valores de niveles haya quedando de la siguiente manera:

NIVEL	DESCRIPCION	NIVEL1	NIVEL2	NIVEL3	CANTIDAD
1	A	A			10
2	B		B		15
3	C			C	20
1	D	D			12
2	E		E		14
3	F			F	15

[MARRASARG1974]

Muchas gracias por tu respuesta! Funciono perfecto!

[v-jingzhan-msft · Accepted solution]

Hi @MARRASARG1974 

Here is my solution. Steps:

1. Duplicate NIVEL column and DESCRIPCION column.

2. Add prefix string "NIVEL" to [NIVEL - Copy] column. 

3. Pivot [NIVEL - Copy] column and select [DESCRIPCION - Copy] column for values, using "Don't Aggregate".

4. Sort table by DESCRIPCION column ascendingly. 

5. Reorder columns. 

Full code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkMDpVidaCUjINMJxDUFc42BTGcgNoLIghS7gGSN4IpdQVwTuGI3qN5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NIVEL = _t, DESCRIPCION = _t, CANTIDAD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NIVEL", Int64.Type}, {"DESCRIPCION", type text}, {"CANTIDAD", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "NIVEL", "NIVEL - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "DESCRIPCION", "DESCRIPCION - Copy"),
    #"Added Prefix" = Table.TransformColumns(#"Duplicated Column1", {{"NIVEL - Copy", each "NIVEL" & Text.From(_, "en-US"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[#"NIVEL - Copy"]), "NIVEL - Copy", "DESCRIPCION - Copy"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"DESCRIPCION", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"NIVEL", "DESCRIPCION", "NIVEL1", "NIVEL2", "NIVEL3", "CANTIDAD"})
in
    #"Reordered Columns"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!