Remove text between two words in a csv file

[laganlee]

Hi all

In Power Query, I want to delete all the text between the words 'Options' and 'Total' in a csv file.

It looks like this: ( I want to get rid of the red characters)

-------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-------------------------------------------------------------------------------

Started : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Source : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Dest : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Files : *.*

Options : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

------------------------------------------------------------------------------

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

------------------------------------------------------------------------------

Total Copied Skipped Mismatch FAILED Extras
Dirs : 1 0 2 0 0 0

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Many thanks for looking!

[laganlee]

Thanks a lot! That's just what I needed.

Found out I can extract by delimiter and put into a new column; so it's just moving and deleting columns until I get what I want.

Many thanks

[Daryl-Lynch-Bzy · Accepted solution]

HI @laganlee  - You should be able to use the Text.BetweenDelimiters - PowerQuery M | Microsoft Docs function to find the text string that needs to be removed in the following step.  The start delimiter will be "Options" and end delimiter will be "Total".

See the following:

let
    Source = "-------------------------------------------------------------------------------#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)-------------------------------------------------------------------------------#(cr)#(lf)#(cr)#(lf)Started : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)Source : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)Dest : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)#(cr)#(lf)Files : *.*#(cr)#(lf)#(cr)#(lf)Options : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)#(cr)#(lf)------------------------------------------------------------------------------#(cr)#(lf)#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx#(cr)#(lf)#(cr)#(lf)------------------------------------------------------------------------------#(cr)#(lf)#(cr)#(lf)Total Copied Skipped Mismatch FAILED Extras#(cr)#(lf)Dirs : 1 0 2 0 0 0#(cr)#(lf)#(cr)#(lf)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    #"Text to Remove" = Text.BetweenDelimiters( Source , "Options" , "Total" ),
    #"Remove Text" = Text.Replace( #"Source" , #"Text to Remove" , " ")
in
    #"Remove Text"