Forum Discussion

Beyondforce's avatar
Beyondforce
Icon for Helper I rankHelper I
9 years ago
Solved

Combining Tables with extra columns!

Hey All,

 

I'm going get a data (CSV files) from a folder (Folder connection). In that folder, I have old files and new files with extra column like in the picture.

In the new files I have new columns, Year, Month and Day, and they are all numbers from the Date column.

 

1. How do I import all the files into a new table with the extra columns?

2. After the import, how do I extract the Year, Month and Day from the old files date into the right columns?

3 Can the above be done at the same time?

 

Thanks.

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Beyondforce,

    As I test, please click Edit Query and open the Query Editor as follows.



    1. How do I import all the files into a new table with the extra columns?

    Please select the Date field and click Date buttom(highlighted in red line) under Add colum above, add the year, month and day columns.



    2. After the import, how do I extract the Year, Month and Day from the old files date into the right columns?

    You can select the column and drag it left or right to correct position. 

    3 Can the above be done at the same time?

    Drag the column position, to confirm your column order of new table and older table are same. Then you can append them together. More details, please refer to this blog.

    Best Regards,
    Angelia

3 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    In correspondence with your other question, I put everything in 1 query.

     

    Something like this:

     

    let
    Source = Folder.Files("........."),
    Filtered = Table.SelectRows(Source, each [Extension] = ".csv"),
    TransformBinary = Table.AddColumn(Filtered, "TransformedBinary", each 
        let
            Source = Csv.Document([Content],[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
            #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
            #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}}),
            AddColumns = if List.Contains(Table.ColumnNames(#"Changed Type"),"Year")
                         then #"Changed Type"
                         else let
                                  AddedDateRecord = Table.AddColumn(#"Changed Type","DateRecord", each Date.ToRecord([Date])),
                                  Expanded = Table.ExpandRecordColumn(AddedDateRecord, "DateRecord", {"Year", "Month", "Day"}, {"Year", "Month", "Day"})
                              in
                                  Expanded,
            #"Reordered Columns" = Table.ReorderColumns(AddColumns,{"Year", "Month", "Day", "Date", "Column1", "Column2", "Column3", "Column4"})
        in
            #"Reordered Columns"),

    Renamed = Table.RenameColumns(TransformBinary, {"Name", "Source.Name"}),
    Removed = Table.SelectColumns(Renamed, {"Source.Name", "TransformedBinary"}),
    Expanded = Table.ExpandTableColumn(Removed, "TransformedBinary", Table.ColumnNames(TransformBinary[TransformedBinary]{0})),
    Typed = Table.TransformColumnTypes(Expanded,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Date", type date}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}})
in
    Typed

     

    Remark: I removed the "Columns" field from the second parameter (record) in function Csv.Document.

    • Beyondforce's avatar
      Beyondforce
      Icon for Helper I rankHelper I
      9 years ago

      Hi MarcelBeug,

       

      Thank you very much for taking the time to help me, I really appreciate it.

      One thing I haven't mentioned, that I am a newbie! That mean, it will take me sometime to figure out what changes do I need to make to make it work. But I'll update you as soon as get any results.

       

      Cheers.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Beyondforce,

    As I test, please click Edit Query and open the Query Editor as follows.



    1. How do I import all the files into a new table with the extra columns?

    Please select the Date field and click Date buttom(highlighted in red line) under Add colum above, add the year, month and day columns.



    2. After the import, how do I extract the Year, Month and Day from the old files date into the right columns?

    You can select the column and drag it left or right to correct position. 

    3 Can the above be done at the same time?

    Drag the column position, to confirm your column order of new table and older table are same. Then you can append them together. More details, please refer to this blog.

    Best Regards,
    Angelia