Calculate for Comma Separated Values

[yogeshmaney]

Hello

Have Data as

Unit      |  Category 

5           |  Cat1,Cat2

3           |  Cat2

Want Result as

Cat1  = 5

Cat2  = 8

[Eric_Zhang · Accepted solution]

Check this Power Query demo, it would help re-model your source from

To

let
    Source = Table.FromRows({{5,"Cat1,Cat2,Cat3"},{2,"Cat2"},{3,"Cat1,Cat2,Cat3"}},{"Unit","Category"}),
    Splitedsrc=Table.AddColumn(Source ,"splitCategory",each Table.FromList(Text.Split([Category],","))),
    ExpandedSplitCatesrc=Table.ExpandTableColumn(SplitedSrc, "splitCategory", {"Column1"}, {"splitCategory"})
in
    ExpandedSplitCateSrc

Then I think it is not a problem doing the calculation in your post.