cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
FacundoAybar
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! 

 

FacundoAybar_0-1693855536680.png

 

2 REPLIES 2
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
AlexanderPrime
Solution Supplier
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!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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