Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hola comunidad.
Su ayuda para saber como hacer lo siguiente en power query:
La tabla original esta así:
Atributo | Valor |
Etiqueta despegada | 1 |
Etiqueta despegada | Amarillo |
Fugas | 0 |
Fugas | Verde |
Dañado | 2 |
Dañado | Rojo |
Y quiero llegar a algo así;
Atributo | Valor | Clasificación |
Etiqueta despegada | 1 | Amarillo |
Fugas | 0 | Verde |
Dañado | 2 | Rojo |
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.
Solved! Go to Solution.
Hi @adrian2402,,
Result
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
Hi @adrian2402,,
Result
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
9 | |
8 | |
6 | |
6 |