Extract numbers from a string and add a space

[Anonymous]

Hi Folks,

I have a field called "Justification" in a feed that comes to Power BI and a user requested to have a column added that extracts the numbers only. So for example, the data on a row could be "HYP Code 2034221 as a replacement for worker 501223" -- to get these numbers in a dedicated column I wrote the following as a custom column: 

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

So this creates a column that yields the result 2034221501223 in a row and will change based on what is in each row of the justification column. The user has now requested that the sets of numbers be spaced out and wants to see it as 2034221 501223 in the row. Is this possible?

Nice to have:

Additionally, they want ANOTHER column that returns 7 digits that begins with a 2 or 6. So they want another column that returns just the 2034221 and ignores anything else that is not 7 digits together. But that could be anywhere in the text. A few examples are:

Replacing Tom Jones with code 2034211 and his EE ID is 201222 -->shoudl return only 2034211

Replacing Betty Smith with code 6032211 and her EE ID is 34111 -->Should return only 6032211

Replacing Mike John (122332) with hyp code 2333222 --> Should return only 2333222

Replacing Rick for project 27+ with code 6211111 --> Should return only 6211111

Hope this makes sense!

Many thanks!

-M

[Anonymous · Accepted solution]

Hi @Anonymous 

(1)For your first question , you can realize it in Power Query Editor . Add a customer column with the formula below .

Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Justification],{"0".."9"," "}))

You will get the result below :

(2)For your second question , you can split the custom column you created before. You will get the following result.

Then go back to Desktop view, to judge whether these two columns can returns 7 digits that begins with a 2 or 6 . If yes , return 1 , or return 0 .We create two measures to judge .

find 1 =
var _firstnumber=LEFT(SELECTEDVALUE('Table'[Custom.1]),1)
var _number=LEN(SELECTEDVALUE('Table'[Custom.1]))
return IF(_number=7 &&_firstnumber="2" || _firstnumber="6",1,0)

find 2 =
var _firstnumber=LEFT(SELECTEDVALUE('Table'[Custom.2]),1)
var _number=LEN(SELECTEDVALUE('Table'[Custom.2]))
return IF(_number=7 &&_firstnumber="2" || _firstnumber="6",1,0)

The result is as shown :

Then create a measure to return the value that contain 7 digits and begin with a 2 or 6 .

final result = SWITCH(TRUE(),[find 1]=1,SELECTEDVALUE('Table'[Custom.1]),[find 2]=1,SELECTEDVALUE('Table'[Custom.2]))

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

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

[Anonymous]

Thank you so much! I never would have figured that out!!!!

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc2xCsIwFIXhVzlkUnRIbqruYgcFF3ULHSSNJJYmJQ1I395Yi3W7XPjOrxS7hRan4E2Pl0sWOtQGxGVBQuDua1jXoyxxPCAfxAURsWqt2N6kNODaftAst1zST5o4S1kIIUZ4do3JReuxyGNS0vLr7dBNdZmfU+XidINHiOhieBqdQLvVX26TW+Nu9QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Column1],{"0".."9"," "})),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Custom] <> null and Text.Length([Custom])=7 and List.Contains({"2","6"},Text.Start([Custom],1)))
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Note:  I changed the sample data slightly to test the 2/6 rule.

[Anonymous]

Thank you so much!!!!!