Transformar datos delimitados

[Anonymous]

Hola

Tengo un conjunto de datos que se ve como abajo;

Apellido	Apellido	Employeeid	Resultado
Ryan Alan Ryan	Arrowsmith Delaney Delaney	123456-456789-456133	Positivo Positivo Negativo

Me gustaría convertirlo a esto;

Apellido	Apellido	Employeeid	Resultado
Ryan	Arrowsmith	123456	Positivo
Alan	Delaney	456789	Positivo
Ryan	Delaney	456133	Negativo

Gracias

[edhans · Accepted solution]

Hola @kersplash, pruebe este código:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqpMzKtxzAESYJZXalqako6SY1FRfnlxbmZJRo1LKlAytRJOB4NEgUoMjYxNTM1qgNjcwhJEGRob15iCAVA2IL84sySzLLUGzvBLTU+EMPJLFIJLi1KVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FirstName = _t, Surname = _t, EmployeeID = _t, Result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", type text}, {"Result", type text}}),
    FirstName = List.Transform(Table.Column(#"Changed Type","FirstName"), Splitter.SplitTextByDelimiter("|")){0},
    Surname = List.Transform(Table.Column(#"Changed Type","Surname"), Splitter.SplitTextByDelimiter("|")){0},
    EmployeeID = List.Transform(Table.Column(#"Changed Type","EmployeeID"), Splitter.SplitTextByDelimiter("|")){0},
    Result = List.Transform(Table.Column(#"Changed Type","Result"), Splitter.SplitTextByDelimiter("|")){0},
    #"Combined Lists" = List.Zip({FirstName,Surname,EmployeeID,Result}),
    #"Converted to Table" = Table.FromList(#"Combined Lists", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"FirstName", "Surname", "EmployeeID", "Result"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", Int64.Type}, {"Result", type text}})
in
    #"Changed Type1"

Como puede ver, esto funciona 4 elementos, y debe funcionar con un número indefinido.

Se convierte...

Sólo tienes que cambiar el nombre de tus columnas a lo que quieras que sean. (EDITAR: Cambié al código para cambiarles el nombre a los nombres originales en el paso de división. Sin embargo, no se molestó en subir una imagen revisada)

[Anonymous]

@edhans ¡Perfecto! ¡Hace lo que dice en la lata!

Gracias a todos.

[edhans]

Gran @kersplash . Me alegra que su proyecto pueda seguir adelante.

[Greg_Deckler]

Sé @ImkeF tiene trucos para esto, también @edhans .

[Vvelarde]

@kersplash

Hola, prueba esto:

1. Despivotar columnas

2.Split por delimitador

3.Transponer

4. Promover encabezados

saludos

Victor

[Anonymous]

Gracias por esto.

¿Sabe cómo permitir números variables de componentes delimitados, es decir. más campos separados por el símbolo de tubería, sin tener que editar la consulta?

[Ashish_Mathur]

Hola

Tomando una pista de @Vvelarde solución, pruebe este código M

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", type text}, {"Result", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", Int64.Type}, {"Result", type text}})
in
    #"Changed Type2"

Esto funcionará para tantas columnas como siga agregando.

Espero que esto ayude.

[Anonymous]

¿Solo obtengo 3 filas usando este código?

[Ashish_Mathur]

Hola

No sé a quién está respondiendo, pero si soy yo entonces eso es lo que el resultado debe mostrar - sólo 3 filas.

[Anonymous]

@Ashish_Mathur Lo sentimos sí, su código sólo me da 3 filas, si agrego más datos todavía obtengo tres filas

[Ashish_Mathur]

Hola

Prueba este código M

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", type text}, {"Result", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Value.1", "Value.2", "Value.3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged", type text}}),
    Partition = Table.Group(#"Changed Type2", {"Attribute"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Merged", "Index1"}, {"Index", "Merged", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}, {"Index1", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Index1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"FirstName", "Surname", "EmployeeID", "Result"})
in
    #"Reordered Columns"

Espero que esto ayude.

[Anonymous]

@Ashish_Mathur Gracias, pero todavía tengo 3 filas sólo

[Ashish_Mathur]

Funciona bien para mí. Descargue mi archivo de Excel desde aquí. Agregue datos por filas en el rango azul y vaya a Datos > Actualizar todo.

Espero que esto ayude.

[Anonymous]

@Ashish_Mathur Lo sentimos, hay un malentendido en los datos adicionales que se añaden.

No son filas adicionales de datos, son datos adicionales en la sola fila con la que comencé, pero solo más datos separados por más símbolos de canalización.

Ejemplo

Apellido	Apellido	Employeeid	Resultado
Ryan Alan Ryan Prueba	Arrowsmith Delaney Delaney Prueba	123456-456789-456133-1234	Positivo Positivo Negativos Positivo

[Ashish_Mathur]

Hola

Prueba este código M

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", type text}, {"Result", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    Partition = Table.Group(#"Split Column by Delimiter", {"Attribute"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Value", "Index1"}, {"Index", "Value", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}, {"Index1", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Index1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"FirstName", "Surname", "EmployeeID", "Result"})
in
    #"Reordered Columns"

[Ashish_Mathur]

Hola

Chris ha resuelto un problema similar aquí. Espero @ChrisHaas puedan ayudarte.