Forum Discussion

vehau1's avatar
vehau1
Helper I
6 years ago

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

  • 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-xicai's avatar
    v-xicai
    Community 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.

    • vehau1's avatar
      vehau1
      Helper 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.