Combining Tables with extra columns!

[Beyondforce]

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.

[v-huizhn-msft · Accepted solution]

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

[MarcelBeug]

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]

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.