Extract certain text

[CJ_96601]

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 be extracted into the new column, if not only number will be extracted, some has delimiter some has not.

Thanks

[v-luwang-msft · Accepted solution]

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

[mahoneypat]

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]

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

Please find correct data sample

Date	Required Result
E1S	E1
E2W	E2
E3S	E3
M2E	2
EE.1	1
EE.2	2
EE.11	11
M11W	11

[v-luwang-msft]

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]

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

Date	Required Result
E1	E1
E2	E2
E3	E3
M2	2
EE.1	1
EE.2	2
EE.11	11
M11	11
EE.01	1
EE.03	3

[v-luwang-msft]

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

[CJ_96601]

Thanks, it works well.

May i kindly ask one more adjustments.  Please refer to last row.

Date	Required Result
E1	E1
E2	E2
E3	E3
M2	2
EE.1	1
EE.2	2
EE.11	11
M11	11
EE.01	1
EE.03	3
EX.A	A

[v-luwang-msft · Accepted solution]

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

[CJ_96601]

Thanks.

May i seek your help as well to get the additional column as follows: (Required Result 1)

Date	Required Result	Required Result1
E1W	E1	W
E2E	E2	E
E3S	E3	S
M2E	2	E
EE.1	1	E
WE.2	2	W
SA.11	11	S
M11S	11	S
EE.01	1	E
SA.03	3	S
EX.A	A	X

[v-luwang-msft]

HI @CJ_96601 ,

What is the new output logic added?

Best Regards

Lucien

[CJ_96601]

Required result 1

If there is no delimiter get the last letter  (S; E; or W)

If there is delimeter and get the first letter (S,E, or W), if the second letter is X get the "X"

Thanks

[v-luwang-msft · Accepted solution]

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

[CJ_96601]

Thanks a lot..