cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StephenF
Resolver I
Resolver I

Isolating 5 digit+ Numbers in Edit Query/Advanced editor

 

I am trying to strip out numbers from a string where the numbers are in a chain of 5 or greater numbers.

 

ie 12345 would be return 12345 but 12 234 wouldnt return anything.

 

I've listed an example of the input and the desired output below

 

Any ideas?

 

InputResults
1223.com IO 71567657156765
IO 159356 Gas Networks europe159356
Loreal Organics 2020 Nar IO: 159718159718
Pharmaton IO: 159722 Audio159722
BGE Shine a Light IO: 159725159725
Volkswagen  IO: 140938 Video140938
IBTS 2019 IO: 8867588675
1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

You can add a custom column with this formula to get your desired result. 

 

Text.Combine(List.Select(List.Transform(Text.Split([Input], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) >=5), ", ")

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
CNENFRNL
Super User III
Super User III

Hi, @StephenF , mahoneypat's solution is neat and effetive to cope with your issue.

If your dataset by chance contains such an entry "1223.com IO 7156765and55555", you may want to try a more generic solution as follows,

 

    #"Added Custom" = Table.AddColumn(Source, "Custom",
        each [
            lst_delim = Text.Remove([Input], {"0".."9"}),
            lst_num = List.Select(Text.SplitAny([Input], lst_delim), each _<>"" and Text.Length(_)>4),
            result = Text.Combine(lst_num,"   ")
        ][result]
    )

 

Screenshot 2020-10-16 162203.png

Yeah, Pats solution will cover 99.9% of cases, I just found one where it was an issue where there was both a 5 and a 7 digit number with only the 7 digit one being valid useful data.

 

I really just will have to have some error tolerance exception handling so it won't crash the query and the data will of course be stored as a text string only at all times.

 

 

mahoneypat
Super User IV
Super User IV

You can add a custom column with this formula to get your desired result. 

 

Text.Combine(List.Select(List.Transform(Text.Split([Input], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) >=5), ", ")

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.