Split columns based on full line content

[sevenhills]

Hi, 

Input to Power BI is a plain text file, which is report output data.  

ReportCodePT100-D-10              Report Date:  15-SEP-2021  04:00     Page 1 of 4

Patient ID    Patient Name                      DOB         Patient City    Has Insurance
Patient100    Patient First, Last, Middle       19901214         San Jose, CA    Yes 
          Next-Visit: 14-Jan-2021 09:30   TYP: Kaiser           Copay: 35.00
                      24-Jan-2021 09:30   TYP: Kaiser           Copay: 55.00
Patient101    Patient First, Last, Middle       19701214    ...             No
          Next-Visit: 14-Jan-2021 09:30   TYP: BlueQWEQE        Copay: 35.00
Patient102    Patient First, Last, Middle       19851219    ...             No
Patient103    Patient First, Last, Middle       19701214    Cupertino, CA   Yes
          Next-Visit: 14-Jan-2021 09:30   TYP: Kaiser           Copay: 35.00
                      24-Jan-2021 09:30   TYP: Kaiser           Copay: 55.00
                      24-Feb-2021 09:30   TYP: Kaiser           Copay: 55.00
                      24-Mar-2021 09:30   TYP: Kaiser           Copay: 55.00
                      24-Apr-2021 09:30   TYP: Kaiser           Copay: 55.00
                      24-May-2021 09:30   TYP: Kaiser           Copay: 55.00

...
...
...

Total CoPay:  $ 4,509.00
Total Records: 999
## END of Report ##

Data rows are Patient details and next visits. I can split into columns as Patient ID, Name, DOB, City, Has Insurance - using fixed position

Patient ID    Patient Name                      DOB       Patient City    Has Insurance

Patient100    Patient First, Last, Middle       DOB       San Jose, CA    Yes 

I can split these columns as Next Visit Date, Insurance Type, Copay Amount - using fixed position

          Next-Visit: 14-Jan-2021 09:30   TYP: Kaiser           Copay: 35.00
                      24-Jan-2021 09:30   TYP: Kaiser           Copay: 55.00
                      24-Feb-2021 09:30   TYP: Kaiser           Copay: 55.00
                      24-Mar-2021 09:30   TYP: Kaiser           Copay: 55.00
                      24-Apr-2021 09:30   TYP: Kaiser           Copay: 55.00
                      24-May-2021 09:30   TYP: Kaiser           Copay: 55.00

How to split both types of data in the same table? Any other better design approach instead of one table?

TIA

[sevenhills]

all the (500+) files are text files, in SharePoint online and planning to get them directly to Power BI. 

Even I do per your instructions, it is more of manual steps. There is no way to automate I believe.

Please let me know a solution and to automate. Regards

[V-pazhen-msft]

@sevenhills 

In your case,  excel should provide a better experience. This is not something Power BI can do, power bi has some integration function like split and merge but not enough to replace data cleaning and integration tools. You have to convert your datasource to a readable layout before importing into power bi. 

Paul Zheng _ Community Support Team
Best Regards

[sevenhills]

Output columns needed are 

• Patient ID
• Patient Name
• DOB
• Patient City
• Has Insurance
• Next Visit Date
• Next Visit Time
• Insurance Type
• Copay