Forum Discussion

campelliann's avatar
campelliann
Icon for Post Patron rankPost Patron
6 years ago
Solved

Extracting text IF these conditions are met (Power Query)

Hi,   I need my coleagues to fill a Project ID field, the problem is that there are many digits, and some people forget to put some zeros. E.g. P2019000000555 some people put P20190555, for example...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, campelliann 

     

    You may try to add a custom column with following codes. The pbix file is attached in the end.

     

     

    let 
len = Text.Length([ID]),id = [ID]
in
if Text.Contains("ID","P20") and len>=9 then
Text.Middle(id,0,5)& Text.Middle(id,len-4,len-1)
else id)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

Like this?

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAyMLQ0AANzI2OlWB2wkBFExMDUyBgq5uhsaGRiCGb7BhgZg5TGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Length([Column1])>9 then Text.Start([Column1],5) & Text.End([Column1],4) else [Column1])
in
    #"Added Custom"