Forum Discussion

markpendlebury's avatar
markpendlebury
Frequent Visitor
7 years ago

Combine Function from csv in folder

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

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Himarkpendlebury ,

     

    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

  • dax's avatar
    dax
    Community Support
    6 years ago

    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.