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 textos en número con caculaciones.
En este momento necesito usar docenas de pasos para procesar estas caculaciones (divididas por ",", luego columnas condicionales, luego sumar juntas). Permítanme saber si hay alguna manera de que se pueda simplificar en PowerQuery - PowerBi Desktop. Apreciado.
Asumiendo
A-4
B-1
C-1
(Delantero/Atrás)
Columna de texto | Resultado |
Frente A | 4/0 |
Atrás A, Espalda C | 0/5 |
Frente B, Espalda C | 1/1 |
Frente A, A trasero A | 4/4 |
Frente A, Frontal B, Delantero C, Atrás B | 6/1 |
Frente A, Delantero A, Delantero B, Atrás B, Atrás C | 9/2 |
@ImkeF , ¿Puedes ayudarte en esto.
Hola @ngct1112 ,
Yo sugeriría fusionarse y agruparse.
Transforme sus números en una tabla adecuada (llamada "Tabla":
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJRitWJVnICsgzBLGcIKxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Character = _t, Number = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Character", type text}, {"Number", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "FB", each {"Front", "Back"}),
#"Expanded FB" = Table.ExpandListColumn(#"Added Custom", "FB"),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded FB", "Text", each Text.Combine({[FB], [Character]}, " "), type text)
in
#"Inserted Merged Column"
Y luego elaborar los cálculos así:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkmtKFFIzs8pzc1T0lEKSi0uzSlRitWJVnIrys8rUXAECproG4BFnBKTsxUcdRTAtDNQwkDfFEmpE5KMob4hsiFQGYhhJqgycM0QhjNUrRNQrRm6KZiaIGqRbLbUN1KKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Text column", type text}, {"Result", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Split([Text column], ", ")),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Custom"}, Table, {"Text"}, "Table", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Number", "FB"}, {"Number", "FB"}),
#"Grouped Rows" = Table.Group(#"Expanded Table", {"Text column", "FB"}, {{"Sum", each List.Sum([Number]), type nullable number}}, GroupKind.Local),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[FB]), "FB", "Sum", List.Sum)
in
#"Pivoted Column"
Esto es bastante dinámico, por lo que puede agregar elementos de cálculo como desee.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF He aplicado esto a mi modelo y funciona perfectamente. ¡Muchas gracias por su ayuda!
Hola @ngct1112 ,
genial para escuchar.
Por favor, marque mi publicación como respuesta y luego 😉
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 |
---|---|
2 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |