Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StephenF
Responsive Resident
Responsive Resident

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
Employee
Employee

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
Community Champion
Community Champion

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

StephenF
Responsive Resident
Responsive Resident

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
Employee
Employee

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


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.