Forum Discussion

CJ_96601's avatar
CJ_96601
Helper V
4 years ago
Solved

Extract certain text

Need help to extract as per below example:

 

DateRequired Result
E1E1
E2E2
E3E3
M22
EE.11
EE.22
EE.1111
M1111

 

If data is E1, E2 and E3, all will be extracted into the new column, if not only number will be extracted, some has delimiter some has not.

 

Thanks

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi CJ_96601 ,

    Adjust to the below:

    if Text.Contains([Date], ".0") 
          then Text.AfterDelimiter([Date], ".0") 
else if  Text.Contains([Date], ".") 
          then Text.AfterDelimiter([Date], ".")       
else if Text.Start([Date],1) = "E" and not Text.Contains([Date], ".")  
          then Text.Start([Date],2)
          else 
         Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Date]),each if Value.Is(Value.FromText(_), type number) then _ else null)))

     

     

    Best Regards

    Lucien

  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi CJ_96601 ，

    New custom column like below:

    = Table.AddColumn(#"Added Custom", "Custom.1", each if not Text.Contains([Date], ".")  then Text.Range([Date],Text.Length([Date])-1,1) 
 else if  Text.Contains([Date], ".")  and Text.Range([Date],1,1)="X" then "X" 
else Text.Start([Date],1))

     

    Best Regards

    Lucien

12 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    You can use an expression like this

     

    = if Text.Contains([Date], ".") then Text.AfterDelimiter([Date], ".") else if Text.Start([Date],1) = "E" and Text.Length([Date]) = 2 then [Date] else Text.End([Date],1)

     

    Pat

    • CJ_96601's avatar
      CJ_96601
      Helper V
      4 years ago

      Thanks, it works, but i made a mistake on the data source.

       

      Please find correct data sample

       

      DateRequired Result
      E1SE1
      E2WE2
      E3SE3
      M2E2
      EE.11
      EE.22
      EE.1111
      M11W11
      • v-luwang-msft's avatar
        v-luwang-msft
        Community Support
        4 years ago

        Hi CJ_96601 ，

        Test like this :

        if Text.Contains([Date], ".") 
          then Text.AfterDelimiter([Date], ".") 
else if Text.Start([Date],1) = "E" and not Text.Contains([Date], ".")  
          then Text.Start([Date],2)
          else 
         Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Date]),each if Value.Is(Value.FromText(_), type number) then _ else null)))

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUQpKLSzNLEpNUQhKLS7NKVGK1YlWcjUMBsq4GkI4RuEgjhGEYwyWMQZzfI1cgRyohKueIZBjCOMYociApSByvoaG4VBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"Required Result", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Date], ".") 
          then Text.AfterDelimiter([Date], ".") 
else if Text.Start([Date],1) = "E" and not Text.Contains([Date], ".")  
          then Text.Start([Date],2)
          else 
         Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Date]),each if Value.Is(Value.FromText(_), type number) then _ else null))))
in
    #"Added Custom"

         

        Did I answer your question? Mark my post as a solution!


        Best Regards

        Lucien