Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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_value | Contacto | Teléfono |
001 | John | (123) 456-7890 |
001 | Larry | (456) 487-9872 |
002 | Jessica | (567) 821-3560 |
002 | Taylor | (348) 971-6150 |
002 | Sinclair | (879) 853-1040 |
003 | Noel | (465) 450-1560 |
003 | Theresa | (789) 453-1324 |
004 | Joan | (465) 798-7848 |
005 | Elizabeth | (798) 755-4657 |
005 | Daniel | (579) 879-8451 |
Datos deseados
contractor_value | contacto1 | teléfono1 | contacto2 | teléfono2 | contacto3 | teléfono3 |
001 | John | (123) 456-7890 | Larry | (456) 487-9872 | ||
002 | Jessica | (567) 821-3560 | Taylor | (348) 971-6150 | Sinclair | (879) 853-1040 |
003 | Noel | (465) 450-1560 | Theresa | (789) 453-1324 | ||
004 | Joan | (465) 798-7848 | ||||
005 | Elizabeth | (798) 755-4657 | Daniel | (579) 879-8451 |
Solved! Go to Solution.
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
Proud to be a Super User!
Estimado @yosemite ,
En función de su descripción, puede realizar algunos pasos de la siguiente manera.
4. Combine todas las columnas excepto la primera columna y deje solo la primera columna y la columna combinada.
5. Utilice el delimitador 'Tab' para dividir la columna 'M'.
6. Cambie el nombre de la columna recién creada.
Resultado:
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.
De nada. Si mi respuesta ayudó, por favor márquela como Respuesta.
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.
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
Proud to be a Super User!
@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(''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
1 | |
1 | |
1 |