Search code examples
excelpowerbipowerquerypowerpivot

How can I sum numbers in the same line in power query?

Good Morning,

I have some doubt in PowerQuery, i have a column with some numbers in the same line that I need to sum, how can I do it?

Example input

field 1    |   field 2 | field 3
Planeacion    2,3,1     2,2,2


field 1   | field 2  | field 3
Planeacion   6           6

output

enter image description here Thanks in advance

Solution

  • In powerquery you can do this

    Click-select the columns with the commas

    Right click, Replace values, and replace the comma with a plus ( , to +)

    Click-select the same columns

    Right click, Transform ... lowercase...

    edit the resulting formula so that each time you see

    Text.Lower, type text

    you replace it with

    each Expression.Evaluate(_), type number

    Sample version for the two columns

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,",","+",Replacer.ReplaceText,{"field 2", "field 3"}),
#"Transform1"   = Table.TransformColumns(#"Replaced Value",{{"field 2", each Expression.Evaluate(_), type number}, {"field 3", each Expression.Evaluate(_), type number}})
in #"Transform1"