Combine Function from csv in folder

[markpendlebury]

I have a structure of folders than all contain .csv files in the same format.

Using folder as the data source Power Bi does a reasonable job of transforming the data.

Each file has data in the following formats.

00000*SOF*DESPATCH0608201908:34:28
2527291,3,001,7322387924,CT4003, 1,31072019,0000516676,,,06082019,73,,
252729*EOF*

I want to remove the first and last rows and keep the data (potentially multiple) rows.

Power BI combine funtion loads the data as below.

I'd like to keep the source.name (filename) on each record as that's useful but then only keep the data rows.

I'm sure I'm close and I've split and transformed similar data before but for this example I just can't get the end result I want.

Any help much appreciated! Thanks

[dax]

Hi markpendlebury, 

Yes, you could click "Use Header as First Row", then you could try to add a step and use below M code in it

= Table.SelectRows(#"Changed Type", each not Text.Contains([name], "*"))

 If this don't work, please correct me and inform me more detailed information

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi@markpendlebury ,

You will have to try using Table.SelectRows along with Text.Contains to filter the *SOF* and *EOF* records.

If you upload the pbix file to Google or OneDrive and share the link here to give you the right solution.

Cheers

CheenuSing