Forum Discussion

Frequent Visitor

3 years ago

Solved

Extract round figure from Text

I try using code below to extract figure from text, it works on round figure but not on float.

Text.Select([TextColumn], {"0".."9"})

This is my output below.

QUANTITY x 4	4
QUANTITY x 4.0	40

The expected output for the second row is 4.

How can I extract only 4 when come to this circumstances?

Tutorial Requests

pls try this

List.Select(
  Splitter.SplitTextByAnyDelimiter({",", ".", " "})(
    Text.Select([Column1], {"1" .. "9", ",", ".", " "})
  ), 
  each _ <> ""
){0}

5 Replies

amitchandak
Super User
3 years ago
Rochejf , First use Text.Replace to remove QUANTITY x and the Use Text.Start to get character 1

Power BI- Power Query Conditional Replace Value- https://youtu.be/Qj2bthdusiM

Power Query - Text.Start, Text.Middle, Text.End: https://www.youtube.com/watch?v=vky4wPqm0O0
- Rochejf
  Frequent Visitor
  3 years ago
  The text.start is not working.
  
  However, I changed the type to whole number after i did the text.replace so it still somehow do the trick?

Super User

pls try this

List.Select(
  Splitter.SplitTextByAnyDelimiter({",", ".", " "})(
    Text.Select([Column1], {"1" .. "9", ",", ".", " "})
  ), 
  each _ <> ""
){0}

Rochejf
Frequent Visitor
3 years ago
It works. However, there's some empty cell in that column also so it will generate error result
- Ahmedx
  Super User
  3 years ago
  pls try this
  
  List.Select( Splitter.SplitTextByAnyDelimiter({",", ".", " "})( Text.Select([Column1], {"0" .. "9", ",", ".", " "}) ), each List.ContainsAny({_},{"0" .. "9"})){0}