Hi John,

Try this solution: https://www.myonlinetraininghub.com/excel-forum/power-query/multiple-csv-files-and-changing-content

Firstly, John, remove that first row in every testx.csv files because it is redundant as it seems the entry in row 2, column B relates to the date give in row 1rnThat has the benefit of allowing Query to use the new first row as the header row for each file.

I then agree with Mynda that you need to create a separate Query for each .csv file ... no big deal in this example.

Then once you have Appended your files, you can unpivot the data, Close & Load it and create a Pivot Table very easily now and analyse your data. 

Using the File attached, any csv files can be combined, no matter how different their headers are.

Of course, like Duncan said, first row is irrelevant and must be removed. The image attached shows how the result looks like.

However, this demonstrates that you can combine from folder, without having to create a query for each source file.

Thanks Catalin

I did take a look at your Get CSV function earlier but when I tried to get it to import all three csv files at once, it asked me to give it a file path rather than a folder path. That led to three files, three queries ... did I use the function wrongly?

@Duncan:

If you tried to open the function, you actually invoke the function and that indeed asks for a file path.

The file is supposed to be used as is, the only change you need to do is to change the FOLDER path from settings sheet. Then you refresh the query, data will be extracted from ALL csv files from that folder with a single query.

If you want to look at the GetCSV function, avoid opening it directly, this action invokes the function and creates a new query. Instead, open another query, from the queries list on the left select the function and open the Advanced Editor, it's the only way to see the code without invoking the function.

Thanks again, Catalin,

I read the file more carefully this time and pasted my own folder path into the first sheet and it worked as you designed it.

I know the Advanced Editor, thanks, even though I will not say I am an M expert at all. I have installed a couple of other functions before. 

I tried an example of my own in your file but I think I have found that your function does not pick up the column headers from those csv files ... I will look at your code after Christmas Day and see what I might suggest ... either because of my file or because of the code.

Merry Christmas if you celebrate it!

Duncan

Hi Duncan,
The function is adjusted for this topic, John's test files have the headers AFTER the first row. You have to remove the line in GetCSV function that removes the first row.
The code for this scenario is:

For a normal csv, the code should not have the Skip first row step:

There you are! Solved.

Thanks, Catalin, I will be using that function from now on!

Duncan

Hi Guys,

Thanks for all the replies so far but one key thing is missing the date in the first row isn't irrelevant.

If you look at the result csv I need to filter by the product and that date, it basically tracks forecasts for example

on the 1st it was forecast that on the 3rd abc would require 1 unit

but on the 2nd the forecast for the 3rd had changed to 2 units

so I need to keep that date in the first row linked to each product in that csv and of course durin a year that will end up at 365 csv's which is why I was trying to avoid a query for each csv file.

at the moment I am looking at 2 querys one to pull the first line date linked to a id based on csv name and a second to do as in the GetCsv function then merge based on the csv name

regards

John

If you need to keep the first value, you can add it as a new column. This is how the function should look like:

(I collected the first cell value in a parameter before removing the first row, then added the value in a new column)

A normal query , will look like:

The only difference between the function and this query is that the first line disappears. That's what converts any normal query to a function.
Because the file path will no longer be provided, hard type the sample file path:
File.Contents("C:CsvFile.csv")

This will allow you to view the steps, add more processing if needed.

After you are done with converting one of the csv files, convert the query back to a function.

This is the best way to build a function. Start with a simple query from one of the files, then convert the query into a function by adding the first row with one or more parameters as needed, building such a custom function is very easy.

I understand the points you make John but I still wonder about that first line. As far as I can tell, the date in cell B2 is exactly the same date that is in cell B3 of all of your files. That is why I said that first line is redundant and it would make your life a little easier if you were to work with cell B3 and remove that first line. Otherwise, Catalin has your problem in hand, I think!