Combine csv files with different amount of colums

[FredEff]

Hi,

I have a csv file for every month of the year.

In every csv file I have the following structure:

Date	01.01.24	01.01.24	02.01.24	03.01.24	04.01.24	05.01.24	05.01.24
Amount	1	2	3	1	1	4	2

I use power query to transform the sample file into this:

Date	Amount
01.01.24	1
01.01.24	2
02.01.24	3
03.01.24	1
04.01.24	1
05.01.24	4
05.01.24	2

The problem I am facing is, that the sample file will determine the amount of colums it will add also for the other files.

So If the data for ex. februrary will also have data for more days, it wont recognize the data as the sample file only had 7 colums.

Is there a way that Power Query will import all colums for all the files?

Thanks!

[Omid_Motamedise · Accepted solution]

Consider this which used From Folder feature for extracting CSV files from a folder with different number of columns as below.

loading them into Power query results in 

remove all the column except Content, then add a new column (from add column tab, pick custom column) and use the below formula.

Table.Transpose(Csv.Document([Content]))

which result in the transppose version for all the csv files like below image

you can revise the previous formula to the next formula to consider the first row as a header.

Table.PromoteHeaders(Table.Transpose(Csv.Document([Content])))

which change the result into the below image,

now expand this new column you can reach the result

[FredEff]

Thank you. This will work. 

Before transposing, I would also need to combine the first 4 columns. 

Can you also help with that? 🙂 

[Omid_Motamedise]

Sure, in this case, you just need to add another column by using the below formula.

Table.FirtsN(Csv.Document([Content]),4)

[lbendlin]

Yes.  You can ignore the "Combine Files"  option that uses the sample file, and create your own process.  Be aware though that Power Query by default includes the number of columns in the meta data for the CSV connection, so you need to remove that attribute to handle variable number of columns.

[FredEff]

Hi and thanks for the reply.

Do you mean, I should not even use the "combine files" function? Or should I just ignore the sample file?

The problem I have then is:

It will paste all files under each other and if I transform then, it just paste all the different files into colums, so I cant work with the data...

[lbendlin]

yes, ignore that function completely. It does way more harm than good.

- connect to the folder

- filter for the CSV files you want

- add a custom column that connects to the CSV file  (without the column limit) and unpivots the columns

- Expand that custom column

Done.

If you like you can provide two sample files (or their sample content).