CODEWITHSUNDEEP
powerbidax
little_grasshopper_14
little_grasshopper_14

Reputation: 43

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

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.

Upvotes: 3

Views: 2141

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

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-

enter image description here

Upvotes: 1

Related Questions