Double header in csv and reading file name

[LakshmanData5]

Hi, I have input file as attached with two rowas as headers. During manufacturing process, system collects every reading 2 times. and device ID is in the file name rather in the data.

https://1drv.ms/x/s!Ai0GzdHCLgzPmAiHeaOgFfN3URd8?e=tgUCrp 

1. How can I read ID from file name?

2. make 2 header rows as single row with proper naming ?

3. Every day system generates 1000 files like this (for every device/ID).

I know using Power Query editor we can edit/manipulate actual data but am not sure about dealing with file name and headers ?

any help is appreciated.

Partial solutions are also apprecieated.

[V-pazhen-msft]

@LakshmanData5 

Not clear to me in the first place. What are the c1,c2... and where is the ID comes from? And what are the table look like in each file? 

For now, if you requirement is feasible I am pretty should use Get Folder connector. 

Best Regards

Paul Zheng

[LakshmanData5]

Hi Paul, C1, C2 are additional columns...these columns have only single row header.

ID should come from CSV source file name.

What am looking is a regular table format. so that I can use it as source.

[LakshmanData5 · Accepted solution]

This video by Vasyl is useful to transform 2 row header to single row header.

https://www.youtube.com/watch?v=Z3Ifa37tXak