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:   Date Required Result E1 E1 E2 E2 E3 E3 M2 2 EE.1 1 EE.2 2 EE.11 11 M11 11   If data is E1, E2 and E3, all will ...
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

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

    • CJ_96601's avatar
      CJ_96601
      Helper V
      4 years ago

      Thanks.   I have some additional data format.  You may refer to the last two items in the table

       

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

       

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

        Hi CJ_96601 ,

        A little adjust:

        if Text.Contains([Date], ".0") 
          then Text.AfterDelimiter([Date], ".0") 
      
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)))

        Return :

         

        Best Regards
        Lucien