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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
AtchayaP
Helper V
Helper V

Powerbi and excel

16554824889504304376756182413262.jpg

Anybody help me?

@amitchandak

11 REPLIES 11
Vijay_A_Verma
Super User
Super User

Use the following formula in a custom column in Power Query

 

= List.Count(Text.Split([skills],","))

 

For DAX

 

= LEN([skills])-LEN(SUBSTITUTE([skills],",",""))+([skills]<>"")

 

I have used the 1st formula, but it's not counting the exact numbers in cell.i have 5skills in one cell, but it's counting as 7.could you help me

Please post your data for me to verify...As long as there are 4 commas to separate the 5 words, it would count 5.

16557440312838568492313443683781.jpg

16557441225004184750829263149417.jpg

16557441593679032384423373778240.jpg

 in worker skills as text colum , has multiple values and count of skills is the exact skills counted from the worker skills as text also custom column I have used the formula you have given 

Right Click on that cell - drill down - Now, post the complete cell value...

IMG_20220620_130237__01.jpg

Please copy the text and paste here. It is not possible to type it from a picture. 

Please post complete text. If the text is very big, you can upload Excel file to Onedrive or any other file host and share the link here. 

Give me that sample which has 5 skills.....What you can do is to randomize them i.e. replace a word with another word to protect confidentiality. Hence if my word is

abc yui, defgh, i, jk, mnop

then I can give

yut 75r, eyuio, k, qw, bnaz

IAM sorry that is confidential I couldn't share..but can you help me with some other way

Actually in excel I can see values but after transforming to pwerbi, it is showing as null.why it is? Is that because of data cleaning is not good.?

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @AtchayaP - Split the Column by Delimiter.  You can split the skills into new rows instead of columns.

Split a column of text (Power Query) (microsoft.com)

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors