How to count the total number of times a word appears using Microsoft Power Bi?

Question

I have a table that contains a list of words and I would like to know how I can use DAX to do the following example:

ID	Items
1	Apple
2	Banana
3	Apple, Banana
4	Orange, Apple

Items	Total
Apple	3
Banana	2
Orange	1

The issue is some of the rows have more than one item and using Count only takes the first item on the list.

Output I don't want:

Items	Total
Apple	2
Banana	1
Orange	1

The current process I did was split the columns, use pivot, then do count.

You can create a separate table based on your source table where you will always have your item wise count. — mkRabbani

mkRabbani mkRabbani · Accepted Answer · 2020-12-22T05:17:00

You can create a separate table based on your source table where you will always have your item wise count. Use this below Advanced Query code for your new table.

Just change the source table name in the code with your original table name.

let
    Source = your_table_name,
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Items", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Items"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Items", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Items", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Items"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Here is the final output-

How to count the total number of times a word appears using Microsoft Power Bi?

1 Answers