Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |