Optional rows process files from a folder

[sevenhills]

Hi 

I have attached two sample files in the zip file and expecting output as below. Trying in power query... any help is appreciated.

Expected columns

Sample data for each column

Column Name	Sample data
File Name	SH Payment 20230601.txt, SH Payment 20230701.txt
Record Number	001, 002 ...
Report Title	Balance Report
Report Date	1-Jun-23
Page	1, 2, ...
Patient ID	100
Age	40
Name	Brenda, Chad T
Sex	F
Address 1	123
Address 2	East 12th Avenue
City	Bellevue
State	WA
Zip 5	98009
Visit Date	10-MAY-2023 10:30AM
Old Visit Date	14-MAY-2023  2:30PM
Old Balance	$0.00
Bill Amount	$345.00
Bill Paid	$345.00
Total Due	$0.00
Balance Note	Balance will be paid in 21 days
Visit Notes	No payment needed (Patient not seen after vitals)

Please note, real data has more optional rows, few hundred files exists that need to be processed and each file having 10K+ rows. Plan is to leverage the solution here later. 

Thanks in advance

[sevenhills]

Update:

Step 1: Able to parse files from the folder and get the file level info (basic step done)

Step 2: Able to parse and get the file record info and patient info (with the help of my other post reply by "ronrsnfld")

i.e., getting these details good

Step 3: Payment info: Having trouble with getting payment details (when it is multiple page records) and linking back ... 

All the payment info rows that are in first page, I am able to get but do not know how to handle if it bleeds into second page ... 

Visit Date	10-MAY-2023 10:30AM
Old Visit Date	14-MAY-2023  2:30PM
Old Balance	$0.00
Bill Amount	$345.00
Bill Paid	$345.00
Total Due	$0.00
Balance Note	Balance will be paid in 21 days
Visit Notes	No payment needed (Patient not seen after vitals)

Note: For some reason, it is not allwoing me to upload the .pbix file, but can upload other files. Like I did for .zip file in the post.

[lbendlin]

Your sample data is very unsuitable for ingestion into Power BI. I would assume that the source system does have a database backend, and you should try getting access to that instead.

[sevenhills]

The source is not database backend. I wish it is database, then it is easy!

The source is only historical files! It is monthly (snapshot) of report data files!

Say, like

     Report_20120101.txt,

     Report_20120201.txt,

     ....

    Report_2023_0801.txt

[lbendlin]

You will need to write your own standalone parser that can take into account all the variations and missing data points, as well as the freeform text (like the Medical Treatment multiline text).  I would suggest VBA or Python for that.

[sevenhills]

That is OK if you don't know. I am looking for others to reply!

I am NOT rude but that is what I am struggling/looking for!  

Coming back, it is the requirement and No alternative! Sorry, I am looking only Power Query Solution!