cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

How do I Count words in multiple columns

Hi,

I want to know how to count how many times appear a word in each column:

Example:  there are 4 columns with replacements of a device, I want to count hoy many times appear each word on the 4 columns,

and use it for a bar graphic that differentiate if 10 of 100 are "SOM", or 20 of 100 are "SD"

PD: Sorry for my english, thanks!

2 REPLIES 2
Super User

Hi,

In the Query Editor, select all columns other than the 4 columns, right click and select "Unpivot Other Columns".  Drag the Values column to a Table/matrix visual and write this measure

Measure = countorws(Data)

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Supplier

Could make a calculated column that does a check for each of the 4 columns for the word in particular if it contains it or not then marks it as a 1 so you can get a row total.

e.g. for the "SD" value

SD_Count =

var _Repuesto_1 = SUM(IF([Repuesto1]="SD",1,0))

var _Repuesto_2 = SUM(IF([Repuesto2]="SD",1,0))

var _Repuesto_3 = SUM(IF([Repuesto3]="SD",1,0))

var _Repuesto_4 = SUM(IF([Repuesto4]="SD",1,0))
RETURN

(_Repuesto_1)+(_Repuesto_2)+(_Repuesto_3)+(_Repuesto_4)

That'd give you a calculated column count for SD, you could then repeat this for the other values you wanted to check. Then you could add each one to a visual

Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors