Append multiple workbooks by column position not by headers

[Rahul_SC]

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 ?

[KT_Bsmart2gethe]

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

[Rahul_SC]

Hi,

I did it as you suggested, but getting this error. 

[KT_Bsmart2gethe]

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

[Anonymous · Accepted solution]

You can just Demote Headers, then combine, nice and easy. 

[Rahul_SC]

This can work. Just I need to add headers at the end manually. 

[KT_Bsmart2gethe]

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:

https://docs.google.com/spreadsheets/d/10TP8s0qyFzs7PNFxuQ7yaDwV7_ae3bJd/edit?usp=sharing&ouid=11302... 

Regards

Kerwin

[Rahul_SC]

Hi, in my case, heading is not same. Just the col position is same. However, thanks for this video, learnt new thing 🙂

[artemus]

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.

[Rahul_SC]

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.