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
Intento hacer lo siguiente:
Tengo un texto en varias columnas en una tabla. Como la siguiente tabla:
Tengo esta mesa y quiero tener el sume de HCL & YF135HTD.. etc. en todo el volumen de fluidos a continuación.
por lo que la salida será
Volúmenes:
HCL: XX
YF135HTD: XX
.. etcetera
apreciamos su ayuda
saludos
Anas
Solved! Go to Solution.
Hola @TAZ_91 ,
Prueba esto:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hcq3DcBAEAPBXhh/oH/5UN52cLj+29CeggEIYs00KSljRYMDPe74K3kyzcyCDS1ODHgiyX+yMGvs6HBhxBtJkfsH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fluid 1" = _t, #"Fluid 1 Volume" = _t, #"Fluid 2" = _t, #"Fluid 2 Volume" = _t, #"Fluid 3" = _t, #"Fluid 3 Volume" = _t, #"Fluid 4" = _t, #"Fluid 4 Volume" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Fluid 1", type text}, {"Fluid 1 Volume", Int64.Type}, {"Fluid 2", type text}, {"Fluid 2 Volume", Int64.Type}, {"Fluid 3", type text}, {"Fluid 3 Volume", Int64.Type}, {"Fluid 4", type text}, {"Fluid 4 Volume", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Fluid 1 Volume", "Fluid 2 Volume", "Fluid 3 Volume", "Fluid 4 Volume"}, "Attribute", "Value"),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1"," Volume","",Replacer.ReplaceText,{"Attribute.1"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [Attribute] = [Attribute.1] then 1 else null),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Attribute.1", "Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Fluid"}, {"Value.1", "Fluid Value"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Fluid", Order.Ascending}})
in
#"Sorted Rows"
Saludos
Icey
Si este post ayuda, entonces considere Aceptarlo como la solución para ayudar a otros miembros a encontrarlo más rápido.
Hola @TAZ_91 ,
Prueba esto:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hcq3DcBAEAPBXhh/oH/5UN52cLj+29CeggEIYs00KSljRYMDPe74K3kyzcyCDS1ODHgiyX+yMGvs6HBhxBtJkfsH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fluid 1" = _t, #"Fluid 1 Volume" = _t, #"Fluid 2" = _t, #"Fluid 2 Volume" = _t, #"Fluid 3" = _t, #"Fluid 3 Volume" = _t, #"Fluid 4" = _t, #"Fluid 4 Volume" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Fluid 1", type text}, {"Fluid 1 Volume", Int64.Type}, {"Fluid 2", type text}, {"Fluid 2 Volume", Int64.Type}, {"Fluid 3", type text}, {"Fluid 3 Volume", Int64.Type}, {"Fluid 4", type text}, {"Fluid 4 Volume", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Fluid 1 Volume", "Fluid 2 Volume", "Fluid 3 Volume", "Fluid 4 Volume"}, "Attribute", "Value"),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1"," Volume","",Replacer.ReplaceText,{"Attribute.1"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [Attribute] = [Attribute.1] then 1 else null),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Attribute.1", "Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Fluid"}, {"Value.1", "Fluid Value"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Fluid", Order.Ascending}})
in
#"Sorted Rows"
Saludos
Icey
Si este post ayuda, entonces considere Aceptarlo como la solución para ayudar a otros miembros a encontrarlo más rápido.
El siguiente vínculo explica cómo despivot pares de columnas (consulte Enfoque 2).
https://datachant.com/2019/10/11/guest-story-unpivot-pairs-of-columns/
Una vez que despivot los pares de columnas, puede crear una medida que sumará Volumen de fluidos.
Proud to be a Super User!
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 |