Forum Discussion

imranamikhan's avatar
imranamikhan
Icon for Helper V rankHelper V
5 years ago
Solved

Combine files but replace column name before expanding

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-data-from/td-p/765847

 

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,

Need Help
  • imranamikhan's avatar
    imranamikhan
    5 years ago

    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-data-from/td-p/765847

6 Replies

    • imranamikhan's avatar
      imranamikhan
      Icon for Helper V rankHelper V
      5 years ago

      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-data-from/td-p/765847

      • Fidezuri1990's avatar
        Fidezuri1990
        Icon for Helper I rankHelper I
        2 years ago

        hi imranamikhan,

        I have a similar problem where the column names have been changed in the Excel Workbook from Jul onwards. Old Column Name:"Number of Late Points"; New column name: "Number of Late Delivery Points".

         

        So from what I gather on your solution, I have to replace the Sample File with the new one that has new column names. 

        When you said run the binaries function as normal in Step 2. What do you mean by that? Thanks. I am quite a beginner in Power BI. 

  • imranamikhan's avatar
    imranamikhan
    Icon for Helper V rankHelper V
    5 years ago

    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's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      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