Forum Discussion

GillesDesbois's avatar
GillesDesbois
Regular Visitor
6 years ago
Solved

Select a variable part in another cell

hello my question is : I have a column with data like : 02009bat2555 - 3605054429035.pdf 3605052926581 nouveau logo.pdf I need to extract only the 13 digits "360..." in a new column. You ...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    I used 5 for my count number, rather than 13.

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi GillesDesbois ,

Yes it is possible. I used PowerQuery. I used Text.PositionOf, and Text.Middle.

See pic and Adv Editor. You could combine this into one column, but left it this way for ease of understanding.

 

Please let me know if you have any questions.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

e

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYzMDTVK0hJU9JRUlCK1YGImFlYAvlgromJqakZUMzUFCwSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOf([Column1], "360")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Middle([Column1],[Custom],5))
in
    #"Added Custom1"