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)}})
RobbeVL's avatar
RobbeVL
Impactful Individual
7 years ago

Hi there,

 

Could you make a copy of your code here?
Might be easier to have a look in.

 

Robbe

dehmos27's avatar
dehmos27
Frequent Visitor
7 years ago

RobbeVL,

 

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"