Feb 10 2021 08:43 PM
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!
Feb 11 2021 01:42 AM
@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
Feb 11 2021 01:51 AM
SolutionWhy don't you use From Folder connector and adjust only one Transform from Sample File query?
Feb 11 2021 04:39 AM
@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:
Feb 11 2021 08:43 AM
@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!
Feb 11 2021 12:50 PM
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!
Feb 11 2021 03:03 PM
@Claudia000 glad it was helpful. And thanks @Wyn Hopkins for sharing my video!
Feb 11 2021 01:51 AM
SolutionWhy don't you use From Folder connector and adjust only one Transform from Sample File query?