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...
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)
@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
Super User
Super UserHola
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"