merging with semi colon and add totals

[Archiedog1]

Hi I have a data set from sql - i need to create a graphic that combines and makes a total 

currently the cell has multiple numbers seperated with a ; e.g - 1000; 100; 200

i need a new column that combines these values to be 1300.

thanks

[Archiedog1]

how do i do this is power bi?

[ronrsnfld]

Cannot help now as I am not at my computer. But in the future, I would suggest you pose that kind of question in the dax section rather than the power query section

[Archiedog1]

i did and they told me to post here

[ronrsnfld · Accepted solution]

That seems odd to me since this is supposed to be a Power Query forum.

But to do this in DAX, something like the below seems to work.

Sum = 
   VAR _path = SUBSTITUTE([Column1],";","|")
   VAR _count = PATHLENGTH(_path)
   VAR _series = GENERATESERIES(1,_count,1)
   var mytable = 
        ADDCOLUMNS(
            _series,
            "mySum", VALUE(PATHITEM(_path,[Value])))
RETURN 
    SUMX(mytable,VALUE([mySum]))

[ronrsnfld]

Split and sum:

   #"Sum Column1" = Table.AddColumn(Source, "Sum Col 1", (r)=> 
        List.Sum(
            List.Transform(
                Text.Split(r[Column1],";"),
            each Number.From(_))), 
        Int64.Type)