Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
FacundoAybar
Regular Visitor

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

 

4 REPLIES 4
Whibley92
Helper II
Helper II

Hello, I have a question about this too but didn't want to make a new post. My data:

 

How did you find the overall learning experience - Please select all that apply - max 4
thought provoking    ;practical ;empowering;inspiring;
easy to apply    ;practical ;empowering;relevant;
empowering;relevant;inspiring;thought provoking    ;
thought provoking    ;practical ;empowering;worthwhile;
thought provoking    ;empowering;relevant;worthwhile;
thought provoking    ; easy to apply    ;practical ;relevant;
thought provoking    ;practical ;relevant;worthwhile;
worthwhile;practical ;thought provoking    ; easy to apply    ;
thought provoking    ;practical ;relevant;worthwhile;
thought provoking    ;practical ;empowering;relevant;
thought provoking    ;practical ;empowering;relevant;
inspiring;reactive to audience;
thought provoking    ;empowering;worthwhile;relevant;

 

I applied the Split Columns by delimiter but now I want to show the word count totals in a chart. 

 

How do I count and merge 4 columns.

Hi,

In the Split by Delimiter window, expand Advanced Options and select Rows.  Now all responses will appear in a single column.  You should now be able to build your chart.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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