Forum Discussion
Append multiple workbooks by column position not by headers
Hi all,
I have multiple excel workbooks which I want to consolidate. The problem is column headers are different and data type can also be different in multiple workbooks. However, I know the column's position is same in each file. So, I want to append the data of all the different workbooks into one workbook irrespecting of column headers and data type.
How can we do this ?
9 Replies
You can use Table.ToRows(table) to convert a table to a list of list of values. Next you can use either rows1 & rows2 & rows3 ... or List.Combine({rows1, rows2, rows3, ...}) to combine them into a single list. Finally use Table.FromRows(rows) to turn it back into a table.
Hi, thanks for responding. But, as I am new, I think I need some visual explanation for using this formula. I am just confused how to apply this formula, in what sequence.
Hi Rahul_SC ,
I created a file and video regarding this topic in Excel Power Query. Let me know if it helps. However, if you are using Power BI, then, I will share another you something else.
Data type is not an issue if all the to be appended columns are the same data type (i.e. irrespective column 1 ..5 but the actual [Name], [Address], [Age]).
File:
Regards
Kerwin
Hi, in my case, heading is not same. Just the col position is same. However, thanks for this video, learnt new thing 🙂
This can work. Just I need to add headers at the end manually.
Hi Rahul_SC ,
Please see below code combine files with different column header name but same column position:
let
// Replace blue text with your folder path
Source = Folder.Files("C:\Users\cktan\Documents\PQ Training"),
RemoveHiddenFiles = Table.SelectRows(Source, each not Text.Contains([Name],"~")),//FirstN formula to get the first row (that's the first file on the list and header only)
GetHdrs = Table.FirstN(Table.AddColumn(RemoveHiddenFiles, "GetTbl", each Table.FirstN(Excel.Workbook([Content]){[Kind = "Sheet"]}[Data],1)),1),//Skip formula skip the row, in this case I skip 1 to exclude the headers
GetTbls = Table.AddColumn(RemoveHiddenFiles, "GetTbl", each Table.Skip(Excel.Workbook([Content]){[Kind = "Sheet"]}[Data],1)),//Combine Hdr and Data(No header)
CombineHdrs2Tbls = Table.Combine({GetHdrs,GetTbls}),//Expand Data
GetTbl = Table.Combine(CombineHdrs2Tbls[GetTbl]),
#"Promoted Headers" = Table.PromoteHeaders(GetTbl, [PromoteAllScalars=true])
in
#"Promoted Headers"Workbook for below code:
Combine Files with different header but same column position
Regards
KT
Hi,
I did it as you suggested, but getting this error.
Hi Rahul_SC ,
Which step will it takes you to if you click on the "Go to Error"?
GetHdrs step:
You should expect something like this.
GetTbl Step:
You should expect something like this.
CombineHdrs2Tbls step:
You should expect something like this.
Once we are able to get it works, it should run it dynamically.
Regards
Kerwin
