Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Finding the space after the text

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.

 

 

ItemDesired Result (No Space)
123450
12345  1
12345-001 1
456   3
345  2
124-pkds 1
124-001iv0
123450
4356780
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Hi, 

       

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

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      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.

       

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    Hi Saxon10 

    Create a Calculated Column with this

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

    Regards

    Phil