Forum Discussion

JohnLow's avatar
JohnLow
Helper I
5 years ago
Solved

Extracting certain text

Hi,

 

Sorry if this is an easy one. So I have the following Column with these values:

Sales

Sales - Purchases

Sales - Compressers

 

I would like to do this in m query and my expected result would be:

Sales

Purchases

Compressers

 

Can someone please let me know how to do that?

 

Thank you

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi JohnLow 

     

    Download sample PBIX

     

    You can replace the string "Sales - "with nothing to get your desired result.

    = Table.ReplaceValue(Source,"Sales - ","",Replacer.ReplaceText,{"Column1"})

     

    Here's the full example query in my PBIX file (above)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WitWBshR0FQJKi5IzEovRRJ3zcwuKUouLU4uA4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"Sales - ","",Replacer.ReplaceText,{"Column1"})
in
    #"Replaced Value"

     

    Regards

    Phil

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi JohnLow 

     

    Download sample PBIX

     

    You can replace the string "Sales - "with nothing to get your desired result.

    = Table.ReplaceValue(Source,"Sales - ","",Replacer.ReplaceText,{"Column1"})

     

    Here's the full example query in my PBIX file (above)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WitWBshR0FQJKi5IzEovRRJ3zcwuKUouLU4uA4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"Sales - ","",Replacer.ReplaceText,{"Column1"})
in
    #"Replaced Value"

     

    Regards

    Phil