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.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

In the query editor, just highlight that column and click on Replace Values in the ribbon.  Enter "000000" and "null" in the two fields in the popup (w/o the quotes), and hit ok.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat