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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors