Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"
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
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |