Forum Discussion

Helper II

6 years ago

Solved

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

Hi I have trouble to perform batch import from multiple excel files which are located in a particular folder where in some files column names differs for example in some file table column name menti...

Need Help

Jimmy801

6 years ago

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

Syndicate_Admin

5 years ago

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

Jimmy801

Community Champion

6 years ago

Hello all,

k_mathana yesterday asked how to rename a column, that on multiple tables are called differently. And so I provided a possible solution to that. After that it turned out that there is also a need to read a folder, to read a Excel-file and afterwards to apply my solution. I asked to closed that threat and open a new one for reading folders and Excel-files. So k_mathana did, but asked again the same question. The question of this threat would be actually how to read a folder with multiple Excel-files.

This could be rather tricky, as you need to know how exactly your Excel-files are looking like (sheets, tables, printing areas?, all the same name?).

k_mathana try this

- Add a new folder source and select your folder

- Transform it

- Filter the table to the files you need (maybe all, then keep it, otherwise filter for .xlsx or name or whatever)

- Add a new custom column and use this formula "Excel.Workbook([Content], true)",

- Expand the column, by clicking on the right side of your newly created column and click okay. You will get now all objects (sheets, tables, etc) of all your Excel-file.

- Filter them (by the column "Kind") to sheet only or table or whatever you need to read.

After this you will have a list of tables, that contains your data.

Next please read one Excel-sheet how you need to and apply all transformation you need (promote headers, reformatting etc.). Post the code here and I will transform the solution of renaming the columns adapt to your needs. Then we can do the final step to apply the function to the filtered tables of your Excel-files created above

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

k_mathana

Helper II

6 years ago

Mr. Jimmy, Thanks for the response. Here the code for my 4 sample files import.

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"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Week ID", "Task", "A", "Date", "Week ID2", "B", "C", "ID", "WEEK"}, {"Week ID", "Task", "A", "Date", "Week ID2", "B", "C", "ID", "WEEK"})
in
#"Expanded Data"

Jimmy801

Community Champion

6 years ago

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

k_mathana
Helper II
6 years ago
Mr. Jimmy, Jimmy801
Thats a perfect solution. Thank you so much for your efforts and time. Exactly thats what I am looking for!
- Jimmy801
  Community Champion
  6 years ago
  Hello k_mathana
  
  great to read that my effort was good for something😊
  
  all the best
  
  Jimmy

Syndicate_Admin

Administrator

5 years ago

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

Forum Discussion

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

Related content

Date difference with multiple criteria

Import multiple files from external source

How to avoid people creating multiple times the same table in different workspaces

Multiple PDFs Different Tables

Different schema into different destination