Forum Discussion
Remove text between two words in a csv file
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!
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"
2 Replies
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"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
