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.

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, campelliann 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

Table:

 

You may add a custom column with following codes in 'Query Editor'.

let 
len = Text.Length([ID]),id = [ID]
in
if 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.

 

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

    Hi Allan,

     

    This almost works, the problem is that I have some text fields which lenght is >= 9, and I want them to stay the same.

     

    Ist it possible, instead of using the condition len>=9,  if text contains "P20??".

     

    Many thanks

    • v-alq-msft's avatar
      v-alq-msft
      Icon for Community Support rankCommunity Support
      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.