Addition a specific text in multiple columns in a table

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.