Forum Discussion
Transformar datos delimitados
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
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)
16 Replies
- Ashish_Mathur
Super User
Hola
Chris ha resuelto un problema similar aquí. Espero @ChrisHaas puedan ayudarte.
- Vvelarde
Community Champion
Hola, prueba esto:
1. Despivotar columnas
2.Split por delimitador
3.Transponer
4. Promover encabezados
saludos
Victor
- 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.
- Greg_Deckler
- edhans
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)
- edhans
Gran @kersplash . Me alegra que su proyecto pueda seguir adelante.
