Forum Discussion

dehmos27's avatar
dehmos27
Frequent Visitor
7 years ago
Solved

How can I replace table column names before expanding data from multiple tables in a folder?

I am combining multiple tables in a folder. I am at the point where I can click Expand Data and expand all of the columsn. However, I need to rename some of the columns before expanding. Is there a w...
Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

    No problem, this should look like so then:

     

    Table.TransformColumns(<PreviousStepName>, {{"Data", each Table.RenameColumns(_, Table.ToRows(Mappings), MissingField.Ignore)}})
ImkeF's avatar
ImkeF
Community Champion
7 years ago

Hi

just replace <PreviousStepName> by the name of your previous step:

 

Table.TransformColumns(<PreviousStepName>, {{"Data", each Table.RenameColumns(_,{{"Name", "Customer"}}, MissingField.Ignore)}})

MissingField.Ignore make sure that no error is thrown when the column "Name" doesn't exist in any of the tables. Also you can add more rename pair-lists if needed.

 

 

dehmos27's avatar
dehmos27
Frequent Visitor
7 years ago

ImkeF ,

 

Wow. That is so very close (and really helpful). The only thing I need to do is change the {"Name","Customer"} block so the results are pulled from a separate table similar to a lookup.

 

In my example, I have a separate table called Mappings that is configured as follows:

 

Column          Corrected

Name             Customer

Location           Office

 

Is it possible to configure the code so that it checks the corresponding table for each column header and then renames if it finds a match?

 

Thank you again for your help.

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    No problem, this should look like so then:

     

    Table.TransformColumns(<PreviousStepName>, {{"Data", each Table.RenameColumns(_, Table.ToRows(Mappings), MissingField.Ignore)}})
    • dehmos27's avatar
      dehmos27
      Frequent Visitor
      7 years ago

      ImkeF, all I can say is "Wow!" both to the speed of your response and to the solution. That worked like a charm. Thank you so very much. I was banging my head against a wall trying to figure that out.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Can i follow up with this question? I have the samiliar issue. The original data comes with different 2nd column name "XXkisngihudg" but it is the same column saying "FileIDs". How to handle this issue? 

       

      My data looks like this. PowerBi reads in using Row2 as default header. Can I use row 1 as default header, like "Column1", "Column2","Column3",.... This dynamic FileIDs gave me a headache to merge different data files. 

       

      Thanks!