Powerbi and excel

[AtchayaP]

Anybody help me?

@amitchandak

[Vijay_A_Verma]

Use the following formula in a custom column in Power Query

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

For DAX

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

[AtchayaP]

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

[Vijay_A_Verma]

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

[AtchayaP]

 

 

 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 

[Vijay_A_Verma]

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

[AtchayaP]

 

[Vijay_A_Verma]

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. 

[Vijay_A_Verma]

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

[AtchayaP]

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

[AtchayaP]

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]

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)