Forum Discussion
Import Report output file
Thanks for your reply.
My question was related to and adding more details:
a) Any tips on modeling of importing this kind of files?
More details:
- I have 100,000 files for over 150 reports, in one linux folder. I got them to Azure.
- I created power bi data flows, one per report type and contains multiple queries in each dataflow
- Note: we can have all in one data flow instead of one per report type. But decided to go one flow per report type.
b)I am able to get the data from each file and parse. But, how to identify the header rows in between pages and strip them of? (as each file has first few rows dynamic and also comes in between pages)
More details:
- Before posting and asking in the forum, I am able to do the combine and transform files per report.
- Each snapshot report output file has header rows in between pages
- Issue is dynamicness involved in each file to process the header rows in each page. I want to strip them.
- After stripping all the header and footer rows, it will be easy create data as columns and use for visualization.
Hope this clarifies the needs 🙂
Thanks
For (b), using power query, I solved it as below
* Step 1: Analyze and Identified the sets as (hard coded for each report)
sets: page header, table header, footer row, page end row ...
This is more for categorizing
* Step 2:
Get data all the files, and line number in each file
| File Name | Row Number |
| File 1 | 1 |
| File 1 | 2 |
| File 1 | 3 |
| File 1 | … |
| File 2 | 1 |
| File 2 | 2 |
| File 2 | 3 |
| File 2 | … |
| File 3 | 1 |
| File 3 | 2 |
| File 3 | 3 |
| File 3 | … |
For each file, go through each row and based on the data identified first row of each set
* Step 3: Remove the dynamic text involved in these rows like removing page number. say,
ReportID_100 Report Name: "Antibiotics Group" Report Date: 12/06/2019 Report Page 2
as
ReportID_100 Report Name: "Antibiotics Group" Report Date: 12/06/2019 Report Page
* Step 4: look the whole table with these category rows and mark the first row (and last row of each set) and create summary of rows for each file as that need to be deleted as
Delete Row Details as
| File Name | Set Name | Row Begin | Row End |
| File 1 | Page Header | 1 | 6 |
| File 1 | Table Header | 7 | 9 |
| File 1 | Page Header | 22 | 27 |
| File 1 | Table Header | 28 | 31 |
| ... | |||
| File 1 | Report End | 1011 | 1011 |
* Expand by adding custom column to the table:
{[Row Begin]..[Row End]}
* Later did left anti join to get only data rows.
🙂Hope it helps for others later
(moving this code logic to dataflow, which is outside this post, FYI)