Finding the space after the text

[Saxon10]

Hi,

I have item column that item column contain text & number. The item column conatin the sapce after the text(item).

I would like to know the no of spaces added after the text.

Item	Desired Result (No Space)
12345	0
12345	1
12345-001	1
456	3
345	2
124-pkds	1
124-001iv	0
12345	0
435678	0

[PhilipTreacy · Accepted solution]

Hi @Saxon10 

Download sample PBIX file

This will work in Power Query.  Create a new column with this code.  A full sample query is in the file above.

= Table.AddColumn(Source, "Num Spaces", each Text.Length([Item]) - Text.Length(Text.Trim([Item])))

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @Saxon10 

Create a Calculated Column with this

NumSpaces = LEN([Item])-LEN(TRIM([Item]))

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @Saxon10 

Download sample PBIX file

This will work in Power Query.  Create a new column with this code.  A full sample query is in the file above.

= Table.AddColumn(Source, "Num Spaces", each Text.Length([Item]) - Text.Length(Text.Trim([Item])))

Regards

Phil

[Saxon10]

Hi, 

Thank you so much for your help and sorry for the late reply. Your solution is working very well. 

[Saxon10]

Thanks for your quick reply.

I would like to achieve the same result by using DAX Measure and New calculated column.

Can you please advise.