Issue while combining multiple CSV files with multiple inconsistent column names and numbers

[CR21]

Hello All,

I am trying to resolve a problem (Combining multiple CSV file with inconsistent column names and numbers). 

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 (Please refer to screenshot). When I change the bold line with the following (which was the original line), there is no error and all data is loaded successfully.

#"Expanded Transform File (3)" = Table.ExpandTableColumn(ReadyToExpand, "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))) 

Now, since I want to combine multiple csv files with inconsistent columns, how can i resolve this issue?

Please advise.

[visheshjain]

Hi @CR21 ,

The screenshot is not clear enough, but still here are 2 approaches that you can use.

Please have a look at the following video, unless you haven't already and hopefully it works out for you.

Managing changing column names in Power Query #10: (M)agic (M)ondays - YouTube

Another way that you can do, is that I can see that you have an 'Invoked Custom Function' step in your query.

I am assuming that the column sort order will be the same in your CSV files and at some point in that function query you have Promoted First Rows to headers.

So before Promoting Headers, you can try to replace the item in the first row, based on another value that will be constant in your headers, using this code. (Beware of case sensitivity in Power Query)

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each[ColumnX],each if Text.Contains([Column3], "Date") then "Location" else [ColumnX],Replacer.ReplaceText,{"Location"}),

In this case, Column3 is my static column which has the header 'Date' using that I am replacing the values in ColumnX with 'Location'.

You can use a similar approach, if the video does not work for you.

Hope this answers your question and if it does please accept is as a solution, so that others can resolve the issue faster.

Thank you,

Vishesh Jain

[v-chenwuz-msft]

Hi @CR21 ,

Hi @CR21 ,

Your screenshot is not very clear.

Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))

Can you explain waht this code mean?

Best Regards

Community Support Team _ chenwu zhu