Forum Discussion

yogeshmaney's avatar
yogeshmaney
Helper I
9 years ago
Solved

Calculate for Comma Separated Values

Hello

 

Have Data as

Unit      |  Category 

5           |  Cat1,Cat2

3           |  Cat2

 

Want Result as

Cat1  = 5

Cat2  = 8

 

 

Show and Tell
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    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. 

1 Reply

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    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.