Forum Discussion
Power Query Find Text and Delete Anything After
Excel Power Query what would be the M Code for finding the String "NUL" and then remove any Text after the "NUL" String. I have several serial numbers for example like
1287JLPO-NUL-00-000-00000
HLQA-00-NUL-000-0000
I just need to delete any text after NUL. As you can see, I can't just replace - or 0 because those could be found prior to NUL.
Thanks in advanced for your help and I will be sure to mark your answer when complete.
With the Text.BeforeDelimiter function Power Query returns the expected result in my example.
let Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], "Changed type" = Table.TransformColumnTypes(Quelle,{{"String", type text}}), "Added custom function" = Table.AddColumn("Changed type", "Before delimiter", each Text.BeforeDelimiter([String],"NUL")&"NUL"), "Removed columns" = Table.RemoveColumns("Added custom function",{"String"}) in "Removed columns"
4 Replies
Replace value => Replace "-NUL-" on "-NUL-@@@"
Extract=>Extract text before delimiter=> "-@@@" (as delimiter)
- I almost tagged you in when I created this post. I appreciate you and others like you who selflessly help others. Very Thankful...
heylookitsme , you are welcome
With the Text.BeforeDelimiter function Power Query returns the expected result in my example.
let Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], "Changed type" = Table.TransformColumnTypes(Quelle,{{"String", type text}}), "Added custom function" = Table.AddColumn("Changed type", "Before delimiter", each Text.BeforeDelimiter([String],"NUL")&"NUL"), "Removed columns" = Table.RemoveColumns("Added custom function",{"String"}) in "Removed columns"
