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. 

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.