Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
adrian2402
New Member

Generar columnas en power query con filas con numeros y texto

Hola comunidad. 

Su ayuda para saber como hacer lo siguiente en power query:

 

La tabla original esta así:

AtributoValor
Etiqueta despegada1
Etiqueta despegadaAmarillo
Fugas0
FugasVerde
Dañado2
DañadoRojo

 

Y quiero llegar a algo así; 

AtributoValorClasificación
Etiqueta despegada1Amarillo
Fugas0Verde
Dañado2Rojo

 

Cabe mencionar que ya he intentado el hacerlo por indice y genera módulo pero hace una especie de suma o conteo raro y no me respeta el valor. 

 

Les agradezco por atelación sus comentarios y ayuda. 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @adrian2402,,

 

Result

dufoq3_0-1720799872462.png

 

v1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    Transform = [ a = List.Distinct(List.Buffer(Source[Atributo])),
    b = List.Split(Source[Valor], 2),
    c = List.Zip({a,b}),
    d = List.Transform(c, (x)=> {x{0}} & x{1} )
  ][d],
    ToTable = Table.FromRows(Transform, type table[Atributo=text, Valor=Int64.Type, Clasificación=text])
in
    ToTable

 

 

v2

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Atributo]), "Atributo", "Valor", each _),
    #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Extracted Values" = Table.TransformColumns(#"Transposed Table", {"Column2", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column2", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Valor", "Clasificación"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Column1", "Atributo"}})
in
    #"Renamed Columns"

 

 

v3

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    Buffered = Table.Buffer(Source),
    GeneratedRecords = List.Generate(
        ()=> [ x = 0,  a = Buffered{x}[Atributo], v = Buffered{x}[Valor], c = Buffered{x+1}[Valor] ],
        each [x] < Table.RowCount(Buffered),
        each [ x = [x]+1, a = Buffered{x}[Atributo], v = if a <> [a] then Buffered{x}[Valor] else null, c = if a <> [a] then Buffered{x+1}[Valor] else null ],
        each [Atributo = [a], Valor = [v], Clasificación =[c]]
),
    ToTable = Table.FromRecords(GeneratedRecords, type table[Atributo=text, Valor=Int64.Type, Clasificación=text]),
    FilteredRows = Table.SelectRows(ToTable, each ([Valor] <> null))
in
    FilteredRows

 

 

v4

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    GroupedRows = Table.Group(Source, {"Atributo"}, {{"All", each #table(type table[Atributo=text, Valor=Int64.Type, Clasificación=text], {{[Atributo]{0}?, Number.From([Valor]{0}?), [Valor]{1}?}}) , type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

 

 

v5

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    Transformed = [ a = List.Buffer(Source[Atributo]),
    v = List.Buffer(Source[Valor]),
    transform = List.Transform(List.Select({0..List.Count(a)-1}, Number.IsEven), (x)=> {a{x}, Number.From(v{x}), v{x+1}}),
    toTable = Table.FromRows(transform, type table[Atributo=text, Valor=Int64.Type, Clasificación=text])
  ][toTable]
in
    Transformed

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

1 REPLY 1
dufoq3
Super User
Super User

Hi @adrian2402,,

 

Result

dufoq3_0-1720799872462.png

 

v1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    Transform = [ a = List.Distinct(List.Buffer(Source[Atributo])),
    b = List.Split(Source[Valor], 2),
    c = List.Zip({a,b}),
    d = List.Transform(c, (x)=> {x{0}} & x{1} )
  ][d],
    ToTable = Table.FromRows(Transform, type table[Atributo=text, Valor=Int64.Type, Clasificación=text])
in
    ToTable

 

 

v2

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Atributo]), "Atributo", "Valor", each _),
    #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Extracted Values" = Table.TransformColumns(#"Transposed Table", {"Column2", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column2", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Valor", "Clasificación"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Column1", "Atributo"}})
in
    #"Renamed Columns"

 

 

v3

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    Buffered = Table.Buffer(Source),
    GeneratedRecords = List.Generate(
        ()=> [ x = 0,  a = Buffered{x}[Atributo], v = Buffered{x}[Valor], c = Buffered{x+1}[Valor] ],
        each [x] < Table.RowCount(Buffered),
        each [ x = [x]+1, a = Buffered{x}[Atributo], v = if a <> [a] then Buffered{x}[Valor] else null, c = if a <> [a] then Buffered{x+1}[Valor] else null ],
        each [Atributo = [a], Valor = [v], Clasificación =[c]]
),
    ToTable = Table.FromRecords(GeneratedRecords, type table[Atributo=text, Valor=Int64.Type, Clasificación=text]),
    FilteredRows = Table.SelectRows(ToTable, each ([Valor] <> null))
in
    FilteredRows

 

 

v4

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    GroupedRows = Table.Group(Source, {"Atributo"}, {{"All", each #table(type table[Atributo=text, Valor=Int64.Type, Clasificación=text], {{[Atributo]{0}?, Number.From([Valor]{0}?), [Valor]{1}?}}) , type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

 

 

v5

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3JLCxNLUlUSEktLkhNT0xJVNJRMlSK1cEh5ZibWJSZk5MPVuFWmp5YDBQ0QOGFpRalpIJFXBIPb0xMyQeKGaHxg/KzgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t]),
    Transformed = [ a = List.Buffer(Source[Atributo]),
    v = List.Buffer(Source[Valor]),
    transform = List.Transform(List.Select({0..List.Count(a)-1}, Number.IsEven), (x)=> {a{x}, Number.From(v{x}), v{x+1}}),
    toTable = Table.FromRows(transform, type table[Atributo=text, Valor=Int64.Type, Clasificación=text])
  ][toTable]
in
    Transformed

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.