Can a Text Column of Comma Delimited Numbers be Totaled?

[Anonymous]

Hello,

I am receiving external excel data to connect and incorporate into Power BI visuals.  This external excel file has a column that lists 1 or more values delimited by comma (screen shot below).  Is there a way in Power BI to sum the delimited number values into a new column/measure?

Example:

Text Value Column = "1788.48, 21331.65"

Desired Column = 23120.13

Thank you,

Ted

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can add a custom column in Power Query Editor to meet your requirement:

List.Sum(List.Transform(List.ReplaceValue(Text.Split([Value],",")," ","",Replacer.ReplaceText),each Number.From(_)))

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VVDJEQQhCExlyrdFASJHLFPmn8YA7mdf9kFz+L6DxhyEKxYwzocIkUAbESpgI18/l1UQ0BOlEuPMd3DmWQhh63xYDQP0Out2Ngb17rL1dq4s16vk4KUIBVBJYl7jOy+VN3eQjHOm0tnt7HS2WO7UVHtQgEhT+6eeVLI4tGnUvrYJ/NqEVb5cVh52lfoSJg2DhOd8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(List.Transform(List.ReplaceValue(Text.Split([Value],",")," ","",Replacer.ReplaceText),each Number.From(_))))
in
    #"Added Custom"

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,

[mtaylor_PMA]

This was fantastic; registered to comment on this forum just to say how grateful I am for this, it was EXACTLY what I needed!

[ChrisMendoza]

@Anonymous -

There might be but, why not just clean it up in Power Query?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcvBCQAxCETRVhbPIjOJMViL2H8bK7l9HvwqIXZuW9CPBGgB5VQYIK0lywk7oSsu0iIfHr+zvOSou3T/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type number}})
in
    #"Changed Type1"