cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Cómo transformar datos con el mismo valor en varias columnas

Hola - Estoy tratando de transformar datos con el mismo contractor_value a columnas separadas; muestra a continuación. Cualquier ayuda es apreciada.

Datos de muestra

contractor_valueContactoTeléfono
001John(123) 456-7890
001Larry(456) 487-9872
002Jessica(567) 821-3560
002Taylor(348) 971-6150
002Sinclair(879) 853-1040
003Noel(465) 450-1560
003Theresa(789) 453-1324
004Joan(465) 798-7848
005Elizabeth(798) 755-4657
005Daniel(579) 879-8451

Datos deseados

contractor_valuecontacto1teléfono1contacto2teléfono2contacto3teléfono3
001John(123) 456-7890Larry(456) 487-9872
002Jessica(567) 821-3560Taylor(348) 971-6150Sinclair(879) 853-1040
003Noel(465) 450-1560Theresa(789) 453-1324
004Joan(465) 798-7848
005Elizabeth(798) 755-4657Daniel(579) 879-8451
1 ACCEPTED SOLUTION

Accepted Solutions
Solution Sage
Solution Sage

@yosemite,

Pruebe esto en Power Query. Copie el código a partir de GroupRows y péguelo en el editor de consultas.

Gracias a @edhans por esta técnica.

let
  Source = Table.FromRows(
      Json.Document(
          Binary.Decompress(
              Binary.FromText(
                  "Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==", 
                  BinaryEncoding.Base64
                ), 
              Compression.Deflate
            )
        ), 
      let
        _t = ((type nullable text) meta [Serialized.Text = true])
      in
        type table [contractor_value = _t, contact = _t, telephone = _t]
    ),
  GroupRows = Table.Group(
      Source, 
      {"contractor_value"}, 
      {
        {"contact group", each Table.SelectColumns(_, "contact")[contact]}, 
        {"telephone group", each Table.SelectColumns(_, "telephone")[telephone]}
      }
    ),
  ExtractValuesContact = Table.TransformColumns(
      GroupRows, 
      {"contact group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  ExtractValuesTelephone = Table.TransformColumns(
      ExtractValuesContact, 
      {"telephone group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  SplitContact = Table.SplitColumn(
      ExtractValuesTelephone, 
      "contact group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"contact group.1", "contact group.2", "contact group.3"}
    ),
  SplitTelephone = Table.SplitColumn(
      SplitContact, 
      "telephone group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"telephone group.1", "telephone group.2", "telephone group.3"}
    ),
  RenameColumns = Table.RenameColumns(
      SplitTelephone, 
      {
        {"contact group.1", "contact1"}, 
        {"contact group.2", "contact2"}, 
        {"contact group.3", "contact3"}, 
        {"telephone group.1", "telephone1"}, 
        {"telephone group.2", "telephone2"}, 
        {"telephone group.3", "telephone3"}
      }
    )
in
  RenameColumns

DataInsights_0-1604182333218.png

View solution in original post

6 REPLIES 6
Solution Sage
Solution Sage

@yosemite,

Pruebe esto en Power Query. Copie el código a partir de GroupRows y péguelo en el editor de consultas.

Gracias a @edhans por esta técnica.

let
  Source = Table.FromRows(
      Json.Document(
          Binary.Decompress(
              Binary.FromText(
                  "Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==", 
                  BinaryEncoding.Base64
                ), 
              Compression.Deflate
            )
        ), 
      let
        _t = ((type nullable text) meta [Serialized.Text = true])
      in
        type table [contractor_value = _t, contact = _t, telephone = _t]
    ),
  GroupRows = Table.Group(
      Source, 
      {"contractor_value"}, 
      {
        {"contact group", each Table.SelectColumns(_, "contact")[contact]}, 
        {"telephone group", each Table.SelectColumns(_, "telephone")[telephone]}
      }
    ),
  ExtractValuesContact = Table.TransformColumns(
      GroupRows, 
      {"contact group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  ExtractValuesTelephone = Table.TransformColumns(
      ExtractValuesContact, 
      {"telephone group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  SplitContact = Table.SplitColumn(
      ExtractValuesTelephone, 
      "contact group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"contact group.1", "contact group.2", "contact group.3"}
    ),
  SplitTelephone = Table.SplitColumn(
      SplitContact, 
      "telephone group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"telephone group.1", "telephone group.2", "telephone group.3"}
    ),
  RenameColumns = Table.RenameColumns(
      SplitTelephone, 
      {
        {"contact group.1", "contact1"}, 
        {"contact group.2", "contact2"}, 
        {"contact group.3", "contact3"}, 
        {"telephone group.1", "telephone1"}, 
        {"telephone group.2", "telephone2"}, 
        {"telephone group.3", "telephone3"}
      }
    )
in
  RenameColumns

DataInsights_0-1604182333218.png

View solution in original post

Super User III
Super User III

Hola

Este código M funciona

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [contractor_value = _t, contact = _t, telephone = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"contractor_value"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"contractor_value", "Attribute"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Order", 1, 1, Int64.Type),
    Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Order", "Index"}, {"Value", "Order", "Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Order", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Order"}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Espero que esto ayude.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper II
Helper II

@DataInsights - He editado el código para usar en mi PBIX, que contiene 11 claves, y estoy recibiendo el siguiente error. Cualquier ayuda es apreciada.

Expression.Error: se especificaron 11 claves, pero se proporcionaron 3 valores.

Detalles:

[Lista]

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cts_contractor_value = _t, cts_contactid = _t, cts_name = _t, cts_salute = _t, cts_address = _t, cts_city = _t, cts_state = _t, cts_zip = _t, cts_telephone = _t, cts_fax = _t, cts_email = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"cts_contractor_value"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"cts_contractor_value", "Attribute"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Order", 1, 1, Int64.Type), Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Order", "Index"}, {"Value", "Order", "Index"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Partition" , "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), "Merged.1", "Merged.2"-), "Filas ordenadas" - Table.Sort('Dividir columna por delimitador",''Orden'',''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''escriba text, "en-IN"),"Merged.2", "Index",Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), "Pivoted Column" ? Table.Pivot(''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

De nada. Si mi respuesta ayudó, por favor márquela como Respuesta.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Microsoft
Microsoft

Estimado @yosemite ,

En función de su descripción, puede realizar algunos pasos de la siguiente manera.

  1. Cree una columna de índice en Power Query.
  2. Combine la columna 'contacto' y la columna 'teléfono' como una columna 'combinada' y, a continuación, elimine ambas columnas. ('Tab' separador)
  3. Seleccione la columna "index" para pivotar la columna. Seleccione "No agregar".

v-yuaj-msft_4-1604363262039.png

v-yuaj-msft_5-1604363262041.png

4. Combine todas las columnas excepto la primera columna y deje solo la primera columna y la columna combinada.

v-yuaj-msft_0-1604364823747.png

v-yuaj-msft_1-1604364823750.png

5. Utilice el delimitador 'Tab' para dividir la columna 'M'.

v-yuaj-msft_1-1604363219943.png

v-yuaj-msft_2-1604363219948.png

6. Cambie el nombre de la columna recién creada.

v-yuaj-msft_3-1604363219950.png

Resultado:

v-yuaj-msft_0-1604363195458.png

Espero que mi sugerencia pueda ayudarte.

Saludos

Yuna

Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors