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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Coser una matriz en una fila con valores duplicados

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ónNombreclasePruebaOtrovaloredadMetadatos1Metadatos2

99Bbb90222Ww4yuyVete a dormir
99aa22989jjj9yuyVete a dormir
100uuu88333Tt5
100Ss33977Iii23

En stichiting consigo esto:

IdentificaciónValores.1Valores.2Valores.3Valores.4Valores.5Valores.6Valores.7Valores.8Valores.9Valores.10

99Bbb90222Ww4yuyVete a dormiraa22989
100uuu88333Tt5 Ss33977

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"

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

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"

image.png

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.

View solution in original post

1 REPLY 1
Syndicate_Admin
Administrator
Administrator

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"

image.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors