Forum Discussion

Praveen6245's avatar
Praveen6245
New Member
3 years ago
Solved

Possible Approach

Hi community,

 

I have a requirement where I need to pivot (kind of) a table with column having stacked values. Its like this:
------------------------------

Product name | Ingredients
------------------------------

ABC | Apple, Soda, Vinegar

DEF | Vinegar, Strawberry, Soda

LMN | Mango, Soda, Vinegar

PQR | Honey, Mango

 

Now I need to have a table / visual which has the individual count of each ingredient like this:
-------------------------------
Ingredient | No. of products

-------------------------------

Apple | 1

Vinegar | 3

Soda | 3

Mango | 2

Honey | 1
-------------------------

 

How do we approach such situations which involve splitting of individual parameters and find the count of them?

 

Thanks in advance

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Praveen6245 ,

     

    You can copy my code and paste it into advanced editor.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRciwoyEnVUQjOT0nUUQjLzEtNTyxSitWJVnJxdQNKQ0WACkqKEsuTUouKKiGKwWp8fP2AanwT89LzsRkREBgElPbIz0sFagKrUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product name " = _t, Ingredients = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product name ", type text}, {"Ingredients", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Ingredients", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ingredients"),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter"," ","",Replacer.ReplaceText,{"Ingredients"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Ingredients", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Ingredients"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    In the Query Editor, use the Split column to split the Ingredients column into rows.  To your visual, drag the Ingredients column to the visual.  Write this measure

    Count = countrows(Data)

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Praveen6245 ,

     

    You can copy my code and paste it into advanced editor.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRciwoyEnVUQjOT0nUUQjLzEtNTyxSitWJVnJxdQNKQ0WACkqKEsuTUouKKiGKwWp8fP2AanwT89LzsRkREBgElPbIz0sFagKrUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product name " = _t, Ingredients = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product name ", type text}, {"Ingredients", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Ingredients", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ingredients"),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter"," ","",Replacer.ReplaceText,{"Ingredients"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Ingredients", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Ingredients"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.