Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Extract number from text string based on condition...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Extract number from text string based on conditions

09-20-2018
06:53 PM

Hi All,

I have done extensive searching and I don't believe this is a repeat, but is definitely and extension of previous questions. I am attempting to extract numbers from a text string within a Power BI function. I have successfully extracted the numbers from the string into a value using the below:

Text.Combine( List.RemoveNulls( List.Transform( Text.ToList([string_col]), each if Value.Is(Value.FromText(_), type number) then _ else null) ) )

Using this code works great when the number I am interested in is the only number in the string, for example:

"Bring on the **1234567** comments" results in **1234567**

However, I can't resolve extracting my number when multiple different numbers occur in the string, for example:

"Bring on on the **1234567** comments with **50** telling me this is a repeat" results in **123456750**

What I need to do is one pull the number within the string that meets conditions (one in my case). For my particular issue, the number I need to extract will always be the only 7 digit number in the string, so I feel like this should be a more straight forward answer?

Is there a way to extract only the 7 digit number using my provided function or something similar? If I am way off base, can someone please set me on the proper path?

As always, the communities help is greatly appreciated.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-21-2018
04:12 AM

Here's one method:

- Create a function using the following code. This function takes a text input and returns the first 7-digit number found (if any).
- Invoke this function to create a custom column

(InputText as text) => let RequiredLength = 7, Digits = {"0".."9"}, CharacterList = Text.ToList(InputText), FirstNumber = List.Accumulate( CharacterList, "", (String,CurrentChar)=> if Text.Length(String) = RequiredLength then String else if List.Contains(Digits,CurrentChar) then String & CurrentChar else "" ) , ReturnValue = if Text.Length(FirstNumber) = RequiredLength then FirstNumber else null in ReturnValue

The function works by taking the characters of InputText from left to right, and building up a string of numbers (stopping when 7 numeric characters are accumulated), otherwise resetting to an empty string when it encounters a non-numeric character.

Here's another idea using table grouping to group consecutive digits together (also a function):

(InputText as text) =>

CharacterList = Text.ToList(InputText), CharacterTable = Table.FromList(CharacterList, Splitter.SplitByNothing(), type table[Character = text], null, ExtraValues.Error), AddedIndex = Table.AddIndexColumn(CharacterTable, "Index", 1, 1), AddedDigitFlag = Table.AddColumn(AddedIndex, "Digit", each List.Contains({"0".."9"},[Character]), type logical), DigitGroups = Table.Group(AddedDigitFlag, {"Digit"}, {{"Number", each Text.Combine(Table.Sort(_,{"Index"})[Character]), type text}}, GroupKind.Local), FilterNumbersLength7 = Table.SelectRows(DigitGroups, each [Digit] = true and Text.Length([Number])=7), FirstNumber = try FilterNumbersLength7{0}[Number] otherwise null in FirstNumber

Another option might be using some R code to find text matching an appropriate regular expression.

Regards,

Owen 🙂

My Blog

Connect on Twitter

Connect on LinkedIn

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-21-2018
04:12 AM

Here's one method:

- Create a function using the following code. This function takes a text input and returns the first 7-digit number found (if any).
- Invoke this function to create a custom column

(InputText as text) => let RequiredLength = 7, Digits = {"0".."9"}, CharacterList = Text.ToList(InputText), FirstNumber = List.Accumulate( CharacterList, "", (String,CurrentChar)=> if Text.Length(String) = RequiredLength then String else if List.Contains(Digits,CurrentChar) then String & CurrentChar else "" ) , ReturnValue = if Text.Length(FirstNumber) = RequiredLength then FirstNumber else null in ReturnValue

The function works by taking the characters of InputText from left to right, and building up a string of numbers (stopping when 7 numeric characters are accumulated), otherwise resetting to an empty string when it encounters a non-numeric character.

Here's another idea using table grouping to group consecutive digits together (also a function):

(InputText as text) =>

CharacterList = Text.ToList(InputText), CharacterTable = Table.FromList(CharacterList, Splitter.SplitByNothing(), type table[Character = text], null, ExtraValues.Error), AddedIndex = Table.AddIndexColumn(CharacterTable, "Index", 1, 1), AddedDigitFlag = Table.AddColumn(AddedIndex, "Digit", each List.Contains({"0".."9"},[Character]), type logical), DigitGroups = Table.Group(AddedDigitFlag, {"Digit"}, {{"Number", each Text.Combine(Table.Sort(_,{"Index"})[Character]), type text}}, GroupKind.Local), FilterNumbersLength7 = Table.SelectRows(DigitGroups, each [Digit] = true and Text.Length([Number])=7), FirstNumber = try FilterNumbersLength7{0}[Number] otherwise null in FirstNumber

Another option might be using some R code to find text matching an appropriate regular expression.

Regards,

Owen 🙂

My Blog

Connect on Twitter

Connect on LinkedIn

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-06-2021
06:37 AM

Hi Owen,

This is great,

I was wondering if you had a solution to extract 2 sets of numbers within the same row;

column x = 1 x 654321 and 2 x 123456

result = 2 columns, column 1= 654321 & column 2 = 123456

so by using your function set to 6 digits extraction, is it possible to extract two sets of (6) occurance digits from the same cell? column x extraction would create two seperate columns one with the first 6 digit number found in column x and the second column with the second occurance of 6 digit number... amended to capture if there were 3 occurance and so on?

cheers,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2020
01:00 AM

Hello Owen, is there any way to get this into a Flow action? I tried getting the file, copying your text into a Compose action, and replacing InputText with the Filename from Dynamic Content, but the result in Body looks like it displays your text, not the evaluated function... Thanks In Advance.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2020
02:32 PM

The M code I posted earlier can't be directly translated to Flow.

I'm sure there is a way of performing the same actions using Flow functions but I don't know enough to tell you what that would be. Perhaps try posting on the Flow forums.

You can use Power Query actions with a SQL Server data source using the Premium "Transform data using Power Query" action, but I suspect that may not be what you are wanting.

Regards,

Owen

My Blog

Connect on Twitter

Connect on LinkedIn

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-23-2020
12:37 AM

@OwenAuger Thank you, no worries some bright spark in the Flow forum translated my requirement into 2 split functions: split at ' - ' and then split at '-'. For completion (and anyone else stuck on a similar problem), this is the link.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2019
09:06 AM

Thank you for that!

If I want to extract only a string that would start with 1 only and have a lenght of 8, what would I need to add? I've been trying a few things but nothing seems to be working.

Thank you!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-01-2019
04:19 AM

Hi,

Could you expand a bit more on the possibility of getting this done using R code? I have tried this solution for a similar problem but haven't managed to get it to work. Not sure what I am doing wrong but I am not very familiar with using functions so I'm sure it's something silly.

My problem is slightly different though, I cannot use number of characters as there will be other numbers in there which are the same length as the number I am trying to extract. However, I do know my number will always start with 45313 and always be 10 characters long. Any help is appreciated.

Cheers

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2019
01:54 AM

Just in case anyone is struggling with a problem similar to mine, I have managed to solve it using a two step solution.

I added a custom column using the following code:

First Column =

if Text.Contains([YourDataColumn], "45313") then Text.PositionOf([YourDataColumn], "45313") else null

I then added a second column which references the first one using this code:

Second Column =

if [FirstColumn]<>null then Text.Range([YourDataColumn], [FirstColumn], 10) else null

The first column calculates the character position of the sequence I know my number will always start with. The second column uses Text.Range which will select a set number of characters (in my case 10) starting from a particular number of characters onwards in the overall text. Since I'm trying to extract information from an e-mail inbox it is impossible to predict which position the information needed will be in, this is overcome by using the first column in the second one.

Featured Topics

Top Solution Authors

User | Count |
---|---|

319 | |

153 | |

81 | |

71 | |

42 |

Top Kudoed Authors

User | Count |
---|---|

364 | |

223 | |

117 | |

81 | |

74 |