Forum Discussion

heylookitsme's avatar
heylookitsme
Iron Contributor
Apr 16, 2024
Solved

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 HL...
BI & Data Analysis
excel
power bi
  • OliverScheurich's avatar
    OliverScheurich
    Apr 16, 2024

    heylookitsme 

    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"

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 16, 2024

heylookitsme 

Replace value => Replace "-NUL-" on "-NUL-@@@"

Extract=>Extract text before delimiter=> "-@@@" (as delimiter)

  • heylookitsme's avatar
    heylookitsme
    Iron Contributor
    Apr 16, 2024
    I almost tagged you in when I created this post. I appreciate you and others like you who selflessly help others. Very Thankful...

Resources