Combining worksheets with different header columns in an Excel file

[Dan_var]

Hello,

I am trying to combine the following 3 work sheets in an excel file : The combined file should have the headers as in Sheet 1. So sheets 2 and 3 should be appended to Sheet 1. I need to define which columns in Sheets and 2 and 3 should be appended to which columns in Sheet1. Is there a way to do this using Power Query or any other simple way ? The videos that show the Power Query are bit confusing.

sheet 1	REGION	Country Code	UID	EMP ID	NAME	USD
sheet 2	REGION	Country	ID	NAME	LC	AMOUNT
sheet 3	REGION	Country	ID	EMP	LC	AMOUNT

Many a thanks
Regards
Dan

 

[sandy666]

you can try with Table.FromRecords

let
    TFR2 = Table.FromRecords({[REGION = "REGION", Country = "Country Code", ID = "UID", NAME = "EMP ID", LC = "NAME", AMOUNT = "USD"]}),
    TFR3 = Table.FromRecords({[REGION = "REGION", Country = "Country Code", ID = "UID", EMP = "EMP ID", LC = "NAME", AMOUNT = "USD"]}),
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    App2 = Table.Combine({TFR2, Source2}),
    Prom2 = Table.PromoteHeaders(App2, [PromoteAllScalars=true]),
    Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    App3 = Table.Combine({TFR3, Source3}),
    Prom3 = Table.PromoteHeaders(App3, [PromoteAllScalars=true]),
    Append = Table.Combine({Source1, Prom2, Prom3})
in
    Append

 

[Dan_var]

Thank you so much. I'll try your method, the actual columns are lot more. Appreciate your prompt response,

regards

 

[sandy666]

You are welcome
next time post representative example using XL2BB
update your profile about Excel version and OS
have a nice day

 

[Dan_var]

Hello, thanks again for your time,
I created the three tables 1,2,3 and put in your query also. The Result - output is what I would like. For example the Country should bring in Country code, Amount usd from other files etc.

Region	Country	ID	Name	LC	Amount	usd	code	table1
Region	Country Code	UID	Emp ID	Name	usd	curr code		table2
na	1​	xyz	zzzzz	tough	15​	usd
Region	Country Code	UID	Emp ID	Name	Input	usd	code	table3
emea	40​	xyz	zzzzz	good	30​	40​	ukp
Result
Region	Country	ID	Name	LC	Amount	usd	code	output
na	1​	xyz	tough		15​	15​	usd
emea	40​	xyz	good	30​	40​	40​	ukp

But I got the following after the Append step. It is not bringing the date under correct columns and also creates additional columns like Column 1 etc.. For instance UID is coming under Column 8 and not under ID, same for USD, any ideas where am I missing ? Regards, Dan

Column1

Column2

Column3

Column4

Column5

Column6

Column7

REGION

Country Code

UID

EMP ID

NAME

USD

Column8

Column9

Column10

Column11

Column12

Region

Country

ID

Name

LC

Amount

USD

Region

Country Code

UID

Emp ID

Name

usd

na

1​

xyz

zzzzz

tough

15​

na

1​

abc

aaaa

easy

25​

Region

Country Code

UID

Emp ID

Name

Input

emea

1​

xyz

zzzzz

good

30​

emea

1​

abc

aaaa

okay

40

 

[sandy666]

next time post representative example using XL2BB
update your profile about Excel version and OS