Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to extract ID numbers from a text string?

Hi, @ImkeF @Zubair_Muhammad Now I’m trying to solve a slightly more complex situation. I’m trying to extract the 5 and 6 length ID numbers for UK and France. This is my current approach...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous

     

    Try this. I am not 100% sure.

    Please could you paste data in copiable format instead if image

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvXWL0tMTsxLrtS3NDQyMrPX003NLcjJr0xNVYrViVZyC3LUBwtkFqcUJ6YkGpkYx5QaGBin6jtYGIKAia6evlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Page = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Page", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([Page],2)="UK" then
                                                                    Text.BetweenDelimiters([Page],"/", "?",1)
                                                                    else
                                                                    if Text.Start([Page],3)="FRA"
                                                                    then
                                                                    Text.BetweenDelimiters([Page],"@", "-")
                                                                    else
                                                                    null)
    in
    #"Added Custom"

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

@Zubair_Muhammad

Thanks very much. I will try tommorow morning.
Yes, I will paste the data in the right format tommorow morning.

Thanks again

Anonymous's avatar
Anonymous
Not applicable
7 years ago

@Zubair_Muhammad

It works ! :)

Thanks very much. I'm learning a lot about text extraction.



Now, I hope I can solve the full problem I have.

Hopefully you can help me with the last steps :)

Data

UK/vacancy/91226?.-employee<af44g>3.3u22u37666fff_91226
UK/vacancy-employee</21226?.->3.3.u376641 fff21226
UK/vacancy-employee155-</44226?>3.3uu37ff54215f_44226
FRA/emplois vacants.employé.243>/@811114-./3uu37ff263337f_?811114
FRA/emplois vacants /employé?<af4fg>aa/@833114-./uu37ff443211f_?833114
FRA/emplois vacants/employé?a3f44>3. /@133114-./ff4121_?155654133114
UK/vacancy.11-employee<af4fg>3.3u76117u37fff_/79856?79856
UK/vacancy-employee<44>3F/91156?.3.u344447f87ff_91156
FRA/emplois vacants /employé?<af4fg>aa/@133114-./uu37f6442251ff_?133114
FRA/emplois vacants /employé?a568>3./@187114-./711112 ff_? 187114