Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Tengo debajo de la tabla de matriz en Power bi que soy capaz de coser como uno. Pero hay un problema, si tengo valores similares en la misma columna, no obtengo los duplicados. ya que es matriz, hay posibilidades de que mi matriz de entrada tenga valores similares y me gustaría mantener los duplicados. ¿Cómo puedo hacerlo? A continuación se explica con un ejemplo:
A continuación se muestra la tabla de entrada en Power BI:
Identificación | Nombre | clase | Prueba | Otrovalor | edad | Metadatos1 | Metadatos2 |
99 | Bbb | 90 | 222 | Ww | 4 | yuy | Vete a dormir |
99 | aa | 22 | 989 | jjj | 9 | yuy | Vete a dormir |
100 | uuu | 88 | 333 | Tt | 5 | ||
100 | Ss | 33 | 977 | Iii | 23 |
En stichiting consigo esto:
Identificación | Valores.1 | Valores.2 | Valores.3 | Valores.4 | Valores.5 | Valores.6 | Valores.7 | Valores.8 | Valores.9 | Valores.10 |
99 | Bbb | 90 | 222 | Ww | 4 | yuy | Vete a dormir | aa | 22 | 989 |
100 | uuu | 88 | 333 | Tt | 5 | Ss | 33 | 977 |
así que si ves, tenía valores Metadata1 y Metadata2 que venían dos veces para Id 99 y hubiera querido que mi tabla final tuviera dos columnas más con valores de Metadata1 y Metadata2. y también faltan los valores de la columna AnotherValue y age para la 2ª fila. ¿Cómo puedo solucionar esto y obtener todo en stiching por Ids.
Esta es la consulta para la tabla de salida anterior en Power Bi:Here is the query for above output table in Power Bi:
dejar
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BDsAQEIXhu8zaQmmDs4gFO7ZMxO37Rtp00YQvJL8RI4VAikopMGhgjIFzghN78YK8mJJ64px3Jg+83Ftrcv7Fh5Z5zAy9B9ZaOAa4sGV9Xe+7kEnOwVqrfGPfMN0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, class = _t, Test = _t, Anothervalue = _t, age = _t, Metadata1 = _t, Metadata2 = _t]),
#"Columnas renombradas" = Table.RenameColumns(Source,{{"Name", "D0"}, {"class", "D1"}, {"Test", "D2"}, {"Anothervalue", "D3"}, {"age", "D4"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Id", Int64.Type}, {"D0", type text}, {"D1", Int64.Type}, {"D2", Int64.Type}, {"D3", type text}, {"D4", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id"}, "Attribute", "Value"),
#"Columnas combinadas" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", type text}}, "en-US"),{"Attribute", "Value"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Values"),
#"Filas agrupadas" = Table.Group(#"Columnas combinadas", {"Id"}, {{"Values", cada Text.Combine([Values],";"), escriba text}}),
#"Dividir columna por delimitador" = Table.SplitColumn(#"Filas agrupadas", "Valores", Splitter.SplitTextByDelimiter(";", QuoteStyle.csv), {"Values.1", "Valores.2", "Valores.3", "Valores.4", "Valores.5", "Valores.6", "Valores.7", "Valores.8", "Valores.9", "Valores.10"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Values.1", type text}, {"Values.2", type text}, {"Values.3", type text}, {"Values.4", type text}, {"Values.5", type text}, {"Values.6", type text}, {"Values.7", type text}, {"Values.8", type text}, {"Values.9", type text}, {"Values.10", type text}}),
#"Texto extraído después del delimitador" = Table.TransformColumns(#"Changed Type1", {{"Values.1", each Text.AfterDelimiter(_, ":"), escriba text}, {"Values.2", cada Text.AfterDelimiter(_, ":"), escriba text}, {"Values.3", cada Text.AfterDelimiter(_, ":"), escriba text}, {"Values.4", cada Text.AfterDelimiter(_, ":"), escriba text}, {"Values.5", cada Text.AfterDelimiter(_, ":"), escriba text}, {"Values.6", cada Text.AfterDelimiter(_, ":"), escriba text}, {"Values.7", cada Text.AfterDelimiter(_, ":"), escriba text}, {"Values.8", cada Text.AfterDelimiter(_, ":"), escriba text}, {"Values.9", cada Text.AfterDelimiter(_, ":"), escriba text}, {"Values.10", cada Text.AfterDelimiter(_, ":"), escriba text}})
en
#"Texto extraído después del delimitador"
Solved! Go to Solution.
Hay @PoweeeBII,
Pruebe el siguiente código:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BDsAQEIXhu8zaQmmDs4gFO7ZMxO37Rtp00YQvJL8RI4VAikopMGhgjIFzghN78YK8mJJ64px3Jg+83Ftrcv7Fh5Z5zAy9B9ZaOAa4sGV9Xe+7kEnOwVqrfGPfMN0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, class = _t, Test = _t, Anothervalue = _t, age = _t, Metadata1 = _t, Metadata2 = _t]),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"class", type text}, {"Test", type text}, {"age", type text}}, "en-US"),{"Name", "class", "Test", "Anothervalue", "age", "Metadata1", "Metadata2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Id"}, {{"Rows", each Text.Combine([Merged],"; "), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Rows", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), {"Rows.1", "Rows.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Rows.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.1.1", "Rows.1.2", "Rows.1.3", "Rows.1.4", "Rows.1.5", "Rows.1.6", "Rows.1.7"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Rows.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.2.1", "Rows.2.2", "Rows.2.3", "Rows.2.4", "Rows.2.5", "Rows.2.6", "Rows.2.7"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Rows.1.1", type text}, {"Rows.1.2", Int64.Type}, {"Rows.1.3", Int64.Type}, {"Rows.1.4", type text}, {"Rows.1.5", Int64.Type}, {"Rows.1.6", type text}, {"Rows.1.7", type text}, {"Rows.2.1", type text}, {"Rows.2.2", Int64.Type}, {"Rows.2.3", Int64.Type}, {"Rows.2.4", type text}, {"Rows.2.5", Int64.Type}, {"Rows.2.6", type text}, {"Rows.2.7", type text}})
in
#"Changed Type2"
Https://exceloffthegrid.com/power-query-combine-rows-into-a-single-cell/ de referencia
Si el problema sigue sin resolverse, proporcione información detallada del error o el resultado esperado que espera. Hágamelo saber de inmediato, esperando su respuesta.
Saludos
Winniz
Si esta publicación ayuda, entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Hay @PoweeeBII,
Pruebe el siguiente código:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BDsAQEIXhu8zaQmmDs4gFO7ZMxO37Rtp00YQvJL8RI4VAikopMGhgjIFzghN78YK8mJJ64px3Jg+83Ftrcv7Fh5Z5zAy9B9ZaOAa4sGV9Xe+7kEnOwVqrfGPfMN0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, class = _t, Test = _t, Anothervalue = _t, age = _t, Metadata1 = _t, Metadata2 = _t]),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"class", type text}, {"Test", type text}, {"age", type text}}, "en-US"),{"Name", "class", "Test", "Anothervalue", "age", "Metadata1", "Metadata2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Id"}, {{"Rows", each Text.Combine([Merged],"; "), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Rows", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), {"Rows.1", "Rows.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Rows.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.1.1", "Rows.1.2", "Rows.1.3", "Rows.1.4", "Rows.1.5", "Rows.1.6", "Rows.1.7"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Rows.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.2.1", "Rows.2.2", "Rows.2.3", "Rows.2.4", "Rows.2.5", "Rows.2.6", "Rows.2.7"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Rows.1.1", type text}, {"Rows.1.2", Int64.Type}, {"Rows.1.3", Int64.Type}, {"Rows.1.4", type text}, {"Rows.1.5", Int64.Type}, {"Rows.1.6", type text}, {"Rows.1.7", type text}, {"Rows.2.1", type text}, {"Rows.2.2", Int64.Type}, {"Rows.2.3", Int64.Type}, {"Rows.2.4", type text}, {"Rows.2.5", Int64.Type}, {"Rows.2.6", type text}, {"Rows.2.7", type text}})
in
#"Changed Type2"
Https://exceloffthegrid.com/power-query-combine-rows-into-a-single-cell/ de referencia
Si el problema sigue sin resolverse, proporcione información detallada del error o el resultado esperado que espera. Hágamelo saber de inmediato, esperando su respuesta.
Saludos
Winniz
Si esta publicación ayuda, entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
User | Count |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |