Convert Multilple Files with Date in Columns to One DataSet

[mrbajana]

I receive every day csv files in the format with the dates in columns but I need to convert it into a base in powerquery to be able to make the reports. All files are saved in a single folder so I use the folder connector. How can you convert these files into one automatically?

[v-jingzhang · Accepted solution]

Hi @mrbajana 

Since you are using the folder connector, when you connect to the folder and get below table, click combine icon on the Content column and select a sample file for it. We will transform the sample file in next steps.

Select Transform Sample File, perform below steps:

• Use First Row as Headers
• Delete auto-generated Changed Type step in Applied Steps pane if any
• Select the first two columns and unpivot other columns

You will get the sample file like below.

Then select the combined query, you will get all data in it. Remove the source name column if you don't need it.

If you see error like 'can't find column ...', check if there is a Changed Type step in Applied Steps pane. This step will refer to columns by name which may not exist after transforming the sample file, so delete the Changed Type step. You can change column types manually after getting all data. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[mrbajana]

Do you have a pbix example? I try to replicate the syntax but i got an error in the secon step

[Vera_33]

Hi @mrbajana ,

My .pbix file won't help you as the source is in my machine, you won't see anything but error message:DataSource.NotFound

The M code is reading from a testFolder with CSVs, but yours are already Tables, so you have to modify them accordingly otherwise please paste the code to Advanced Editor with a blank query and only change the folder path.

I have no idea what you have done previously to get the Tables in the Column, but if your Tables have Header already, try it - just modified the part start with each

each 
Table.UnpivotOtherColumns([Transformar archivo], {"Teatro", "Ciudad"}, "Fecha", "Monto") )

[Vera_33]

Hi @mrbajana 

Just unpivot them and combine

let
    Source = Folder.Files("C:\Users\---\testFolder"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 
Table.UnpivotOtherColumns(Table.PromoteHeaders(  Csv.Document([Content])), {"Teatro", "Ciudad"}, "Fecha", "Monto") ),
    Custom1 = Table.Combine( #"Added Custom"[Custom])
in
    Custom1