Forum Discussion

vcb_001's avatar
vcb_001
Frequent Visitor
2 years ago

Data manipulation based on an unstructured column

Hello everyone,

 

I'm dealing with some data where the primary key is not structured well, so I should somehow extract the information using power query or any other solution from your point of view is appreciated gratefully.

Column

Chicago | 4524 | University Illinois | U.S.
Berlin | TU Berlin | Germany | 9644
432133_München_Germany_Ludwig-Maximilian University

As you can see it is quite inconvenient and unstructured, what I need basically a new column that only includes the numbers from the above column like the below:

New column

4524

9644

432133

Is there any way to handle this problem from your point of view to extract the desired result I mentioned?

 

Looking forward to having your feedback, and thank you very much for your help and support in advance.

Need Help
Tips and Tricks

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    vcb_001,

     

    Create a custom column in Power Query using this expression:

     

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

     

     

    • vcb_001's avatar
      vcb_001
      Frequent Visitor
      2 years ago

      Hi, thank you for your answer. However, there are some incidents like:

      Passasu | Passau Uni | Germany | AAC82821

       

      and for those incidents, I have to take like "AAC82821" including the char.
       
      Additionally, when there is an incident like (779 University | 212131), this solution retrieves 779212131 where I have to include only 212131. Is there also a condition or function for ignoring those types of strings in the data?