Custom Function processing text into multiple tables

[sevenhills]

Hi

I have hundreds of files with this kind of data. Each file has more than 1000 records. 

Could anyone help to write a custom function(s) to process the data and convert this into rows and columns. I would like to process each file at a time.

Data:

                              Patient Treatment and Visits
Patient ID: 0911	Name: FirstName, LastName 0911
Cigarettes: Yes         Packs per day: 1 pack/day         Cigarettes/pack:  20
Pipe Bowls: N/A         Bowls per day:
Cigars:     Yes         Number per week: 4
Other tobacco products: Sticks      Amount per day/week: 3
E-Cigarettes: N/A            Cartridges per day:       mg/mL    liquid nicotine: 

Medicine: NRT Nasal Spray
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.5 mg of nicotine
Medicine: NRT Oral Inhaler
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.3 mg of nicotine
Medicine: Certizine
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.2 mg
Medicine: Warm Air Inhale Procedure
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine:
Medicine: Steam bath
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine:
VISIT: 07/11/2014  PRIOR VISIT           # OF SAME DOSE: 001
Medicine: Warm Air Inhale Procedure
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.5 mg of nicotine
VISIT: 07/11/2020  PRIOR VISIT           # OF SAME DOSE: 001
Medicine: Certizine
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.2 mg
Medicine: Warm Air Inhale Procedure
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.5 mg of nicotine
VISIT: 07/11/2022  PRIOR VISIT           # OF SAME DOSE: 001
Medicine: Certizine
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.2 mg
VISIT: 16/12/2023  PRIOR VISIT           # OF SAME DOSE: 001
PAYMENT DETAILS

Visit Date:     16-DEC-2023 10:30AM
Old Balance:    $1245.00
Bill Amount:    $2345.00 

DATE VISITED: 12/16/2023          VISIT TYPE: PHONE & OFFICE

+_______________________________________________________________________________
                               Patient Treatment and Visits
Patient ID: 1411	Name: FirstName, LastName 1411

Cigarettes: Yes         Packs per day: 1 pack/day         Cigarettes/pack:  20 GIV

Medicine: Warm Air Inhale Procedure
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.5 mg of nicotine
VISIT: 07/11/2020  PRIOR VISIT           # OF SAME DOSE: 001
Medicine: Certizine
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.2 mg
Medicine: Steam bath
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine:
VISIT: 07/01/2021  PRIOR VISIT           # OF SAME DOSE: 001

PAYMENT DETAILS

Visit Date:     16-JUN-2023 10:30AM
Old Balance:    $245.00
Bill Amount:    $345.00 

DATE VISITED: 07/01/2021          VISIT TYPE: PHONE
+_______________________________________________________________________________
                               Patient Treatment and Visits
Patient ID: 2111	Name: FirstName, LastName 2111

E-Cigarettes: 4             Cartridges per day:   40    mg/mL    liquid nicotine: Yes

Medicine: NRT Nasal Spray
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.5 mg of nicotine
Medicine: NRT Oral Inhaler
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.3 mg of nicotine
Medicine: Steam bath
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine:
VISIT: 07/11/2014  PRIOR VISIT           # OF SAME DOSE: 001
Medicine: Certizine
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine: 0.2 mg
VISIT: 07/11/2020  PRIOR VISIT           # OF SAME DOSE: 001
Medicine: Warm Air Inhale Procedure
Dosing: 3 doses/hour (8-40 doses/day);		Nicotine:
VISIT: 10-JUN-2023  PRIOR VISIT           # OF SAME DOSE: 001

PAYMENT DETAILS

Visit Date:     10-JUN-2023 10:30AM
Old Balance:    $0.00
Bill Amount:    $345.00
Bill Paid:      $345.00
Total Due:      $0.00

DATE VISITED: 06/10/2023         VISIT TYPE: OFFICE
+_______________________________________________________________________________
                               Patient Treatment and Visits
Patient ID: 2234	Name: FirstName, LastName 2234
No Visits
Treatment Notes: Next visit we will go in details.
PAYMENT DETAILS:
Visit Notes: No payment, as no visits.
+_______________________________________________________________________________

I would like the data two tables. Note some of the rows are optional and crazy pattern. I dont have a choice of using direct database or other tools like powershell to prepare the data for consumption. I have to use Power bI only. 

Table: Patient Visits 
Columns: PatientRecord, Patient ID, Name, Cigarettes, Packs per day, Cigarettes per pack, Pipe Bowls, Bowls per day, Cigars, Number per week, Other tobacco products, Amount per day by week, E-Cigarettes, Cartridges per day, liquid nicotine, No Visits, Treatment Notes, Visit Date, Old Balance, Bill Amount, Date Visited, Visit Type, Bill Paid, Total Due, Visit Notes

Table: Patient Medicines

Columns: Medicine, Dosing, Nicotine, Visit Date, Prior Visit, # OF SAME DOSE

Any help is appreciated.

Thanks in advance.

[sevenhills]

I could come up till here 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5Vdtb9MwEP4rp8EHEOvipN2A8ilrMgha02oNQ9OYkJeYziKJi+NQxq/n4rRNOnVdy1oowh8iy/fy3J19L7m83IOlq08VZ6mCQDKqkmJH0wjOecZVtne1f7k3ZfCcNpDXplmJ+jRhbTjhMlPFdh9OabnTfFq4w4dUMqVY1oYLltVQw68ZjJiEiN62wYQRHhi4n3FUkkZBawNYpLSHjxgci3GMKn3Dngnoo5nKCh3ZilVH9/PkGvkK3jFjqLul2XvqBk+UuKZhKGAkRZSHCsUHihfW6mUnIsdgTGCMUrypxd1G3du6aYU7VCrJoyGreV2uZGgkp8Um5t9yHkHKQ6F4ipHVWvWnyyIe6jP/LMC4ZzSGwUjSW011RMbTIZoBkcgwXjcil/DsVaNFJgeI9vzN3K37MxBycIgmgPgyA16A2JMI6KU3NGZyE5DN5ZAdJhX/OT1/JJaFWHf0f6QyAZvLiUvQlyJkUS4fj3cHaKAYTeCaqpvNaD73Bl6ATr00TNOwiNnCTDrzemegCTWxJ9A7gYHddcHpDVyUIOYfi8F9T+qO8Rb5feP/3QeyanCsXQvOxEDzyDCtwsDmugb27Yuu6wfguIHtnQ6q4qZ7DThUsbIkmkcNx+00NIZJ2k1id8vyHEdwTGOahiXjU9NqHR6Qsisc8zieFOeSaDU1sVZEHTtwS2tdbGXoBfpSOjJdpSvBRR/N7r/r+S58ygmxjtCjE6/jVqpefN7s0kph+VqrUZut1Rp1wVe5tdluDW+980Ut7O8n1w5Xnu11DKJdN9d1fe3cff/BXyF3l6XuA5lb9+XezP030tQyV0vTgq8Kx/yg2ZqDXzxn4nOBZZMmJvt/O2zu8Iy21U6+iWq45UF6OnOQqqhst3qRFasXeaB2VbQ+5dHkT69OC4TCR+7kbEqbaVxU8HDmIvOjSr3e1YaT3S94OJbVbvv+god8WtgXdVUVhi/Kv2z2Q8F3fY1jBuMi4kMBPIWIKcrj7GDRA2jXLn+qB//36W2heR9oBqkolU7kNx/Wq18=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LineDetails = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LineDetails", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "LineNumber", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"LineNumber", "LineDetails"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"LineNumber", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "LineSectionType1", each if Text.Contains([LineDetails], "                              Patient Treatment and Visits") then "PatientRecordBegin" else if Text.Contains([LineDetails], "___________________________________________") then "PatientRecordEnd" else if Text.Contains([LineDetails], "Patient ID:") then "Data For Transformations" else null, type text),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column", "LineSectionType2", each if Text.Contains([LineDetails], "                              Patient Treatment and Visits") then "PatientRecordBegin" else if Text.Contains([LineDetails], "___________________________________________") then "PatientRecordEnd" else if Text.Contains([LineDetails], "Patient ID:") then "Patient Visits 1" else if Text.Contains([LineDetails], "PAYMENT DETAILS") then "Patient Visits 2" else if Text.Contains([LineDetails], "No Visits") then "No Visits" else if Text.Contains([LineDetails], "Medicine:") then "Patient Medicines" else null, type text),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "PatientRecord", each if Text.Contains([LineDetails], "                              Patient Treatment and Visits") then "IGNORE" else if Text.Contains([LineDetails], "___________________________________________") then "IGNORE" else if Text.Contains([LineDetails], "Patient ID:") then "Patient ID:" & Text.BetweenDelimiters([LineDetails], "Patient ID: ", "Name:") else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column3",{"PatientRecord", "LineSectionType2", "LineSectionType1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([LineSectionType1] = "Data For Transformations")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"LineSectionType1", "PatientRecord", "LineSectionType2"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"allRows", each _, type table [LineNumber=number, LineDetails=nullable text, LineSectionType1=text, LineSectionType2=text, PatientRecord=text]}})
in
    #"Grouped Rows"

Intermediate output till here shows as:

Help needed is to write custom functions to process each table type.

thanks

[Anonymous]

If you want specifics, that is for a consultant.

But generally, you need to exploit the delimiters. Start by splitting columns (if these are lines, not rows, use the Lines functions to fix that). Next, split by ";", then by first":", then count the spaces between those dosage and nicotine columns and split by that. Then, you need to get creative using Text.BetweenDelimiters, like using "Visit Date:" and "Old Balance:" as the delimiters to get your Visit Date. Use line feeds as delimiters as well. It's going to take some trial and error, and your code will be long. But I do this stuff all the time. It can be done. 

Again, the real key is creative use of Text.BetweenDelimiters. Your delimiters can be whole words or even whole lines, not just single characters.

Hope that helps!!

--Nate