Forum Discussion
[Power Query] how to exclude x number of rows at the top when combining csv files
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!
Why don't you use From Folder connector and adjust only one Transform from Sample File query?
6 Replies
Why don't you use From Folder connector and adjust only one Transform from Sample File query?
Claudia000
Nice blog here about this https://www.myonlinetraininghub.com/power-query-get-files-from-a-folderfrom Mynda
Thanks for the promotion of my video Riny_van_Eekelen 😀
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!
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
