Addition a specific text in multiple columns in a table

[Anonymous]

Hi,

I am try to do the following: 

I have a text in multiple columns in a table. As the table below:

i have this table and  i want to have the sume of HCL & YF135HTD..etc across all Fluid volume below.

so the output will be

volumes: 

HCL: XX 

YF135HTD: XX

.. etc

appreciate your help

Regards,

Anas

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

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"

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[DataInsights]

@Anonymous,

The link below explains how to unpivot column pairs (see Approach 2).

https://datachant.com/2019/10/11/guest-story-unpivot-pairs-of-columns/

Once you unpivot the column pairs, you can create a measure that will sum Fluid Volume.