Forum Discussion
How to combine csv files with inconsistent columns from within a folder?
Hi justlogmein ,
I have downloaded your files and create the code below. you can copy and paste into your dashboard file sample data query in advance editor.
See below modified code (Import from Folder):
let
//You can replace the file with your drill down path to the blue text below
Source = Folder.Files("C:\Users\cktan\Documents\Solutions\justlogmein\Sample Data"),// Add new column to get the files and promote headers before expand the columns
GetTables = Table.AddColumn(Source, "GetTbl", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]))),
//Below code is to get the column names from each csv file and combine them as a list
GetColumnNames = Table.AddColumn(GetTables, "GetColName", each Table.ColumnNames([GetTbl])),
ColNameList = Table.Distinct(Table.ExpandListColumn(Table.SelectColumns(GetColumnNames,{"GetColName"}), "GetColName"))[GetColName],//Keep the name and added column before we expand
Filtered_Columns = Table.SelectColumns(GetTables,{"Name", "GetTbl"}),//the above column name list is use at below code in blue text to allow dynamic expand
#"Expanded GetTbl" = Table.ExpandTableColumn(Filtered_Columns, "GetTbl", ColNameList)
in
#"Expanded GetTbl"I hope this helps
Hello All, I am trying to resolve the same problem (Combining multiple CSV file with inconsistent column names and numbers). But I do face different issues.
After Googling, YouTubing for a solution, I did find more than videos which explains the solution for this challenge. However, I am faced with following error:
So, what I have done so far?
let
Source = SharePoint.Files("<OneDriveLocation where files are stored>", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "OneDrive location where Files are stored")),
#"Filtered Hidden Files" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
PreExpand = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
HEADINGS = List.Union( List.Transform(PreExpand[#"Transform File (3)"], each Table.ColumnNames(_))),
ReadyToExpand = PreExpand,
#"Expanded Transform File (3)" = Table.ExpandTableColumn(ReadyToExpand, "Transform File (3)", HEADINGS)
in
#"Expanded Transform File (3)"
The bold statement is a changed line so as to accomodate all the known columns from all the files in a folder. But it results in an error. When I change the bold line with the following (which was the original line), there is no error and all data is loaded.
#"Expanded Transform File (3)" = Table.ExpandTableColumn(ReadyToExpand, "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)")))
Please advise.