Batch Import multiple excel files with different column name from a folder

Hello k_mathana 

this should be your code

give it a try and let us know if its working.

let

Source = Folder.Files("C:\Users\k_mathana\Desktop\Sample"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content], true)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
ChangeColumnName =(tableint)=>
let
    TransformTable =  #table({"old", "new"},{{"Week ID2", "WEEK"}, {"Week ID", "WEEK"}}),
    FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(tableint), _[old]))),
    RenameWeek = try Table.RenameColumns(tableint, FilterForWeek) otherwise tableint
in
    RenameWeek,
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table")),
GetAllColumnNames =   List.Distinct(List.Combine(Table.TransformColumns(RenameColumnsData, {{"Data", each Table.ColumnNames(_)}})[Data])),
RenameColumnsData = Table.TransformColumns
(
    #"Filtered Rows",
    {
        {
            "Data",
            (tableint)=> ChangeColumnName(tableint)
        }
    }

),
    #"Removed Other Columns" = Table.SelectColumns(RenameColumnsData,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", GetAllColumnNames, GetAllColumnNames)
in
    #"Expanded Data"

In this line you can maintain your names to be renamed

The first value specifies the old, the second the new one. Be aware that Power query is case sensitive

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hello @k_mathana 

this should be your code

give it a try and let us know if its working.

let

Source = Folder.Files("C:\Users\k_mathana\Desktop\Sample"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content], true)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
ChangeColumnName =(tableint)=>
let
    TransformTable =  #table({"old", "new"},{{"Week ID2", "WEEK"}, {"Week ID", "WEEK"}}),
    FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(tableint), _[old]))),
    RenameWeek = try Table.RenameColumns(tableint, FilterForWeek) otherwise tableint
in
    RenameWeek,
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table")),
GetAllColumnNames =   List.Distinct(List.Combine(Table.TransformColumns(RenameColumnsData, {{"Data", each Table.ColumnNames(_)}})[Data])),
RenameColumnsData = Table.TransformColumns
(
    #"Filtered Rows",
    {
        {
            "Data",
            (tableint)=> ChangeColumnName(tableint)
        }
    }

),
    #"Removed Other Columns" = Table.SelectColumns(RenameColumnsData,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", GetAllColumnNames, GetAllColumnNames)
in
    #"Expanded Data"

In this line you can maintain your names to be renamed

The first value specifies the old, the second the new one. Be aware that Power query is case sensitive

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy