Forum Discussion
Tips to dynamically edit text files by recognizing keyword + trimming
This one is a bit tricky:
I have several text files in this format. They differ in size, charlengths and are read by keywords, originally.
I want to be able to produce DYNAMICALLY, the rectangle that is highlighted in yellow.
2 things:
1. Preferrably, I want it to search for the keyword that preceeds this table: "FLUID IN-PLACE REGIONS:" is the keyword. I want power query to remove all rows before this keyword, and remove all rows after the next keyword ("RUNSPEC").
2. The columns in the hightlighted table are seperated by both spaces and tabs, unevenly. I need a bulletproof formula for converting the table into a table that has only one non-text value between the columns (either a single space or a single tab. If there exist both, choose tab)
This is how the final product should look like (I have been able to make it, but not dynamically. I will have to redo the steps for every new file that differs a little bit):
This is an excerpt of the textfile. copy it into a file and load it into power query for applying the steps:
--
-- Model description:
-- ------------------
--
--
--
--
-- Formation Zone No. Simgrid_layer #layers
-- ---------------------------------------------------------------------
-- Upper Tarbert 3 UT3_3 1 1 - 15 15
-- Upper Tarbert 3 UT3_2 2 16 - 20 5
-- Upper Tarbert 3 UT3_1 3 21 - 21 1
-- Upper Tarbert 2 UT2 4 22 - 23 2
-- Upper Tarbert 1 UT1_2 5 24 - 37 14
-- Upper Tarbert 1 UT1_1 6 38 - 46 9
-- Middle Tarbert 2.2 MT2_2 7 47 - 71 25
-- Middle Tarbert 2.1 MT2_1 8 72 - 83 12
-- ---------------------------------------------------------------------
--
-- FLUID IN-PLACE REGIONS: (FIPNUM)
-- ---------------------------------
-- 25 regions, 4 grid segments (laterally): KM, KW, KN, Outside,
-- combined with 8 formation levels (Zones, as in table above)
--
-- Formation Zone KM KW KN KNE KWW Outside
-- ---------------------------------------------------------------------------------
-- Upper Tarbert 3 UT3_3 1 9 17 25 33 41
-- Upper Tarbert 3 UT3_2 2 10 18 26 34 41
-- Upper Tarbert 3 UT3_1 3 11 19 27 35 41
-- Upper Tarbert 2 UT2 4 12 20 28 36 41
-- Upper Tarbert 1 UT1_2 5 13 21 29 37 41
-- Upper Tarbert 1 UT1_1 6 14 22 30 38 41
-- Middle Tarbert 2.2 MT2_2 7 15 23 31 39 41
-- Middle Tarbert 2.1 MT2_1 8 16 24 32 40 41
-- ---------------------------------------------------------------------------------
--
--
--======================================================================
RUNSPEC
--======================================================================
-- Model options, table & data dimensions:
--NOSIM
TITLE
bla bla bla bal
-- Phases
bla
bla
bla
SKIP300
3 Replies
- vehau1Helper I
Also, if someone knows how to do a python script for this, it is great, as I have enabled python scripting in power query. thanks.
- v-xicaiCommunity Support
Hi vehau1 ,
If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support , Scroll down and click "CREATE SUPPORT TICKET", to get further help.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- vehau1Helper I
I created a ticket, however
If someone knows how to:
1) search for keyword and remove all rows above/below keyword
2) trim text so that every non-char is either 1 space OR 1 tab (not space&tab in sequnce, IF that is the case, choose tab)
I'd be happy if you share tips.