Forum Discussion

Beyondforce's avatar
Beyondforce
Helper 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 n...
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

MarcelBeug's avatar
MarcelBeug
Community 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
Helper 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.