Forum Discussion

mrbajana's avatar
mrbajana
Helper III
5 years ago
Solved

Convert Multilple Files with Date in Columns to One DataSet

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?

Need Help
Show and Tell
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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
  • mrbajana's avatar
    mrbajana
    Helper III
    5 years ago

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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") )