Forum Discussion
Optional rows process files from a folder
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.
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
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.
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!
