Forum Discussion
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 way to rename column headers in the "table" values before expanding? As an example, I would want to rename "Name" to "Customer" so any tables with a column for "Name" expand into a single column, "Customer".
No problem, this should look like so then:
Table.TransformColumns(<PreviousStepName>, {{"Data", each Table.RenameColumns(_, Table.ToRows(Mappings), MissingField.Ignore)}})
7 Replies
Hi there,
Could you make a copy of your code here?
Might be easier to have a look in.Robbe
Certainly. Here it is:
let Source = Folder.Files("C:\Users\rhetset\Desktop\PBI Samples"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from PBI Samples", each #"Transform File from PBI Samples"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from PBI Samples"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.ColumnNames([Transform File from PBI Samples])), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Transform File from PBI Samples", "Data"}}), Custom1 = Table.TransformColumns(#"Renamed Columns",{{"Data",each Table.RenameColumns(_,{{"Name","Customer"}},MissingField.Ignore)}}), #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Table Name", "Customer", "Type", "Location", "Type1", "Item", "Site", "Product"}, {"Table Name", "Customer", "Type", "Location", "Type1", "Item", "Site", "Product"}) in #"Expanded Data"
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.
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.
No problem, this should look like so then:
Table.TransformColumns(<PreviousStepName>, {{"Data", each Table.RenameColumns(_, Table.ToRows(Mappings), MissingField.Ignore)}})
