Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |