Combine files but replace column name before expanding

[imranamikhan]

Hi everyone,

I am using the Combine files (binaries) function in Data Flows to append multiple Excel files (with the same structure) into a single table. The sample file I am using is a file which contains the correct column names and structure.

Unfortunately, there are rare occasions when one of the Excel files has a slightly different column name. For example most Excel files have a column header called “Task_Name”, but on rare occasions the column header could be “Name”. This results in Power Query removing that file from the Combine function.

This is a common problem raised in the forums but I cannot seem to fit any of the suggested solutions to my example.

https://community.powerbi.com/t5/Power-Query/How-can-I-replace-table-column-names-before-expanding-d...

Below is the M query automatically generated by Power Query:

let
  Source = SharePoint.Files("https://removed.sharepoint.com/sites/removed-PlanningCoE", [ApiVersion = 15]),
  #"Filtered rows" = Table.SelectRows(Source, each [Folder Path] = "https://removed.sharepoint.com/sites/removed-PlanningCoE/Shared Documents/Planning CoE/removedTemp WIP/"),
  #"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),
  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),
  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file"}),
  #"Removed errors" = Table.RemoveRowsWithErrors(#"Removed other columns", {"Transform file"}),
  #"Expanded table column" = Table.ExpandTableColumn(#"Removed errors", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file")))
in
  #"Expanded table column"

I am assuming I need to apply a column name change prior to the "Removed errors" step, and I was thinking something along the lines of the code below but this has not worked – possibly because placing “Transform file” into the syntax is not correct?

#"Change Name" = Table.TransformColumns(#"Removed other columns", {{"Transform file", each Table.RenameColumns(_,{{"Name", "Task_Name"}}, MissingField.Ignore)}}),

Any direction would be appreciated.

Best regards,

[imranamikhan]

Hi @mahoneypat - any thoughts on this one? Perhaps this works differently in Data Flows?

[imranamikhan]

Thanks Pat. I thought I had followed the steps correctly but unfortunately the same error is appearing ("The column 'Task_Name' of the table wasn't found."

Below is the revised M-Query applied to the Transform Sample file table:

let
  Source = Excel.Workbook(Parameter, null, true),
  Navigation = Source{0}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Renamed columns" = Table.RenameColumns(#"Promoted headers", {{Table.ColumnNames(#"Promoted headers"){2}, "Task_Name"}})
in
  #"Renamed columns"

[mahoneypat]

Your syntax looks correct and that step appears to be working (data are shown in the preview window, not an error).  Have you tried changing the sample file to the one causing the error to work through it directly?

Also, do you have Allow background refresh unchecked (which you should), and just need to Refresh Preview (sorry to ask the obvious).

Pat

[mahoneypat]

Please see this video for how to avoid this.

(15) Power BI - Use relative references to avoid combine & transform errors - YouTube

Pat

[imranamikhan · Accepted solution]

Thanks Pat. I suspect things work a bit differently when using Data Flows. Fortunately I have identified a solution. Steps below in case anyone else has a similar issue:

Instructions:

1. Create a template Excel file containing only the columns you need with the correct header names. This will be the example query that performs all of the required extract steps
2. Run the combine files (binaries) function as normal
3. Select the output query and change the column name. Then copy the renamed column syntax (skip this step if you already know the syntax)
4. Update the syntax of this step by adding "MissingField.Ignore" (this is an optional MissingField argument in the Table.RenameColumns function). For example:
   
   

#"Renamed columns" = Table.RenameColumns(#"Promoted headers",{{"Name", "Task_Name"}}, MissingField.Ignore)​

• Remove the Rename columns step
• Select the “Transform file” function query

• Select Advanced Editor and remove/disable the “Changed column type” step.
• Insert your Renamed columns step - note this is case sensitive and Data Flow M-Query uses a different case than Desktop. Also, update previous step name (in this case the previous step name is “Promoted headers”). Also note the below is an example renaming multiple columns.

let
  Source = (Parameter as binary) => let
  Source = Excel.Workbook(Parameter, null, true),
  Navigation = Source{[Item = "Planning_Task_Export_Table", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Renamed columns" = Table.RenameColumns(#"Promoted headers",{{"II__Project", "II_Project"}, {"Name", "Task_Name"}, {"Text28", "Commentary"}, {"Text5", "Solution_Increment"}, {"Text6", "Project_Increment"}}, MissingField.Ignore)
  //#"Changed column type" = Table.TransformColumnTypes(#"Renamed columns", {{"ID", Int64.Type}, {"Unique_ID", Int64.Type}, {"Task_Name", type text}, {"Start_Date", type datetime}, {"Finish_Date", type datetime}, {"Percent_Complete", Int64.Type}, {"Baseline_Start", type datetime}, {"Baseline_Finish", type datetime}, {"Milestone", type text}, {"Solution_Increment", type number}, {"Project_Increment", type date}, {"Milestone_ID", type text}, {"Milestone_Level", type text}, {"II_Project", type text}, {"Commentary", type text}, {"Unique_ID_Successors", Int64.Type}})
in
  #"Renamed columns"
in
  Source

References:

https://www.excelguru.ca/blog/2017/07/26/merge-files-different-column-headers/

https://community.powerbi.com/t5/Power-Query/How-can-I-replace-table-column-names-before-expanding-d...