Forum Discussion
campelliann
Post Patron
Post PatronExtracting 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...
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.
campelliannPost Patron
Post PatronHi 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
Community Support
Community SupportHi, 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.