Forum Discussion
Transformar datos delimitados
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)
Community Champion
Hola, prueba esto:
1. Despivotar columnas
2.Split por delimitador
3.Transponer
4. Promover encabezados
saludos
Victor
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
Super User
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.
¿Solo obtengo 3 filas usando este código?
