How do I extract a specific text with a specific text length?

[BenScargill]

On Power BI, I am trying to extract a 5 digit number from a column with text and numbers, image below. I can extract the numbers from the column using 'Text.Select([Column Name], {"0".."9"})' but I'm not sure how to say it has to be 5 digits in length. I have seen the code, 'each (Text.Length(_) = 5', but this shows an Error in the new column.

I want to be able to use the below formula but it shows as an error. How can I make this work?

Text.Select([Column Name], {"0".."9"}, each (Text.Length(_) = 5)

Any suggestions?

[AlienSx]

let
    Source = your_table,
    nums = "0123456789",
    func = (txt) => 
        [cln = List.Transform(Text.ToList(txt), (x) => if Text.Contains(nums, x) then x else " "),
        t = Text.Combine(cln),
        split = Splitter.SplitTextByWhitespace()(t), 
        combine = Text.Combine(List.Select(split, (x) => Text.Length(x) = 5), ", ")][combine],
    add_col = Table.AddColumn(Source, "5 digits", each func([Cross hire for Contract]))
in
    add_col

[Anonymous]

Hi all,

The insights of the super users are very good. Here I would like to share a blog that is about executing regular expressions with JavaScript in Power Query Editor.

In this blog, you will be shown how to use regular expressions to get strings in a specific format from a column and I think for this post is a solution.

Blog: Explore Web.Page in power query: Execute regular e... - Microsoft Fabric Community

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Regular expressions with Java Script are min. 100x but mostly 1000x times slower as an complex MCode!

Andreas.

[Anonymous]

Even easier, just append a space to your text column, And then no matter where you're five digits are, you can use Text.BetweenDelimiters with "Cross Hire for Contract" and " " as your delimiters.

--Nate

[dufoq3]

Of course he can do it this way, but it will not extract numbers in case there is a missing space in text like "Cross hire for contract25698"

[Anonymous]

True, but the could first use the replace values function from the GUI to replace "contract" with "contract ", and since that might cause double spaces, use the replace values function to replace "  " with " ".  Now you can use Text.BetweenDelimiters with "Cross Hire for Contract " and " " as your delimiters.

--Nate

[Anonymous]

Add a custom column:

= let 
numbers = Text.Select([Test], {"0".."9"})
in
if Text.Length(numbers)=5 then numbers else null

[BenScargill]

Thank you for your suggestion. However, this does not work as the new column doesn't exist yet.

Can I add a new blank column before and name it?

[Anonymous]

The issue is up to you, why do you change "numbers" to "Contract Numbers"?

Andreas.

[BenScargill]

Hi Andreas,

I tried this but there was an error, see below image...

[Anonymous · Accepted solution]

I am right that you are a beginner with MCode!?

Don't thouch the "x" in this code:

= let 
x = Text.Select([Cross hire for Contract], {"0".."9"})
in
if Text.Length(x)=5 then x else null

Andreas.

[Anonymous]

I can't tell from that sample size, but if all of your values are at the end of the string, you can just use Text.End([ColumnName], 5).

--Nate

[BenScargill]

Hi watkinnc,

Unfortunately, the digits are not always at the end of the string. The numbers are always after 'Cross Hire for Contract text'. But the other values are random.

Hope this helps.

[dufoq3]

Hi @BenScargill, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxBC4JAFATgvzLsOYQ219RjBnUM6iYeFnvqwurGe1vgv2+xa7dhmPnaVjUcRHB1TBgCowlLZNtH6Kowpep2rbq9uZ+skKDGmbz7EK9IDY8k2+BHTH+IXFfbQsNFmqXGKXhPeDkfIu52oLjiEsbRJ7zxZDnLsu3wIInY60NukMTC5McSdnmmUBjVdV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cross hire for Contract" = _t]),
    Ad_Extract = Table.AddColumn(Source, "Extract", each 
        [ a = {"0".."9"},
          b = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains(a, x), a)([Cross hire for Contract]),
          c = List.Transform(b, (y)=> Splitter.SplitTextByCharacterTransition(a, (x)=> not List.Contains(a, x))(y)),
          d = List.Select(List.Combine(c), (x)=> (try Number.From(x) otherwise false) is number and Text.Length(Text.Trim(x)) = 5),
          e = Text.Combine(d, ", ")
        ][e], type text)
in
    Ad_Extract

[BenScargill]

Hi dufoq3,

Thank you for your message.

Is there not a simpler way of doing this code? I'm very new to Power Query and Power BI so I do not understand what this means.

How can I correct the below code when adding a Custom Column...

Text.Select([Column Name], {"0".."9"}, each (Text.Length(_) = 5)

[dufoq3]

If you use Text.Select it will extract each number - for example if you have text: "ABC 12 CD 34 EFG 5" - it will extract "12345" and match your condition which is not correct. Just use my query and it will work as you wish. If you don't know ho to use my query - read note below my posts.