Sum Comma seperated Values using a calculated column

Hi Anonymous  

parry2k has shown you the way by dax. You can achieve your goal by M Query in Power Query Editor as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YGTBgYWBgbWEAosYmANg0YmBkApY1NzA0tTmJZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Refused Quantity(Batch by Batch)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Refused Quantity(Batch by Batch)", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Refused Quantity(Batch by Batch)", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Refused Quantity(Batch by Batch).1", "Refused Quantity(Batch by Batch).2", "Refused Quantity(Batch by Batch).3", "Refused Quantity(Batch by Batch).4", "Refused Quantity(Batch by Batch).5", "Refused Quantity(Batch by Batch).6", "Refused Quantity(Batch by Batch).7"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Refused Quantity(Batch by Batch).1", Int64.Type}, {"Refused Quantity(Batch by Batch).2", Int64.Type}, {"Refused Quantity(Batch by Batch).3", Int64.Type}, {"Refused Quantity(Batch by Batch).4", Int64.Type}, {"Refused Quantity(Batch by Batch).5", Int64.Type}, {"Refused Quantity(Batch by Batch).6", Int64.Type}, {"Refused Quantity(Batch by Batch).7", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"Refused Quantity(Batch by Batch).7", "Refused Quantity(Batch by Batch).6", "Refused Quantity(Batch by Batch).5", "Refused Quantity(Batch by Batch).4", "Refused Quantity(Batch by Batch).3", "Refused Quantity(Batch by Batch).2", "Refused Quantity(Batch by Batch).1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Index"}, {{"Refused Quantity(Batch by Batch)", each List.Sum([Value]), type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Index"})
in
    #"Removed Columns1"

The original Table:

Result:

You can download the pbix file from this link: Sum Comma seperated Values using a calculated column

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.