I have many csv files with different structures. I’m looking to append them with their names added in another column.
The thing is, the UI way offers to import and combine them, but I end up with only the matching columns. I want to append them, as can be done on queries, and essentially have ALL the existing columns.
I got to a stage where I have the files nested in one table, but I can’t figure out how to extract them to different queries (automatically) and then I could append them.
In powerquery you can try this code pasted into home…advanced editor… to compbine all CSV files in a specified directory while preserving the file name that any particular piece of info came from
let Source = Folder.Files("C:temp9"),
#"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
#"Added CSVdata" = Table.AddColumn(#"Filtered Rows", "CSVdata", each Table.PromoteHeaders(Csv.Document([Content])), type table),
#"Added Custom" = Table.AddColumn(#"Added CSVdata", "Custom", each let name = [Name] in Table.AddColumn([CSVdata],"SourceFile", each name)),
#"Combined CSVdata" = Table.Combine(#"Added Custom"[Custom]),
#"Reordered Columns" = Table.ReorderColumns(#"Combined CSVdata",{"SourceFile"}&List.RemoveItems(Table.ColumnNames(#"Combined CSVdata"),{"SourceFile"}))
in #"Reordered Columns"
or
let Source = Folder.Files("C:temp9"),
#"Added Custom" = Table.AddColumn(Source, "CSV", each Table.PromoteHeaders(Csv.Document([Content]))),
List = List.Union(List.Transform(#"Added Custom"[CSV], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "CSV", List,List)
in #"Expanded Data"