[Power Query] how to exclude x number of rows at the top when combining csv files

[Claudia000]

New power query user here (2019 version)!

I started by retrieving a single csv file from a folder and transforming the raw data. The data contained 8 rows at the top that I didn't need so I removed them using 

= Table.Skip(#"Changed Type",8)

The goal here is to be able to combine additional csv files in the same folder and click refresh so that the new data will follow my applied steps. The issue I'm having is that the column names (row 7) of the additional csv files are also being added even though they are within the 8 rows of data.

Row 7 is showing up when combining the data even though I applied the step of removing 8 rows. All csv files have the same number of columns and similar enough column names. What am I missing here? I've tried filtering it out but it doesn't seem to work. Thank you in advance!

[#2]

@Claudia000 You start by doing all the transformations needed on one file. Then you create a parameter for the file name used in the Source step. Replace the hardcoded file name with the parameter. Now, you can create a function of the first query.

Create a table with all the file names you need to transform, query it and add a column through "Invoke Custom function". Select the function you just created, select the column that contains the file names and press OK. Expand the new column.

Actually, @Wyn Hopkins recently posted a link to an easy to follow video that demonstrates this principle. His video deals with named ranges within one sheet, but you apply the same technique to files in a folder.

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

[#3 · Accepted solution]

@Claudia000 

Why don't you use From Folder connector and adjust only one Transform from Sample File query?

[#4]

@Claudia000 

Nice blog here about this  https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder

from @Mynda Treacy 

Thanks for the promotion of my video @Riny_van_Eekelen  :grinning_face:

[#5]

@Wyn Hopkins You're welcome! Always good to promote good work from others :)

Perhaps quite obvious, but your video was an eye-opener. So simple, but yet so clear. And it made me forget about the "From folder" option that @Sergei Baklan mentioned. Am a relative new PQ user as I have been on a Mac since 2011. Still am, but running W10 virtually on Parallels for the past 9 months or so. Still learning, every day!

[#6]

Thanks for the blog post! @Wyn Hopkins@Mynda Treacy The part I found most helpful was Step 6 - I realized I put my transforming queries under my final queries instead of my sample file query. All my files are now being consolidated properly! 

[#7]

@Claudia000 glad it was helpful. And thanks @Wyn Hopkins for sharing my video!