Combining worksheets with different header columns in an Excel file

Dan_var

Board Regular
Joined
Jan 22, 2009
Messages
92
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 1REGIONCountry CodeUIDEMP IDNAMEUSD
sheet 2REGIONCountryIDNAMELCAMOUNT
sheet 3REGIONCountryIDEMPLCAMOUNT

Many a thanks
Regards
Dan
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you can try with Table.FromRecords
Power Query:
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

Board Regular
Joined
Jan 22, 2009
Messages
92
Thank you so much. I'll try your method, the actual columns are lot more. Appreciate your prompt response,

regards
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
You are welcome
next time post representative example using XL2BB
update your profile about Excel version and OS
have a nice day
 

Dan_var

Board Regular
Joined
Jan 22, 2009
Messages
92
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.
RegionCountryIDNameLCAmountusdcodetable1
RegionCountry CodeUIDEmp IDNameusdcurr codetable2
na
1​
xyzzzzzztough
15​
usd
RegionCountry CodeUIDEmp IDNameInputusdcodetable3
emea
40​
xyzzzzzzgood
30​
40​
ukp
Result
RegionCountryIDNameLCAmountusdcodeoutput
na
1​
xyztough
15​
15​
usd
emea
40​
xyzgood
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
Column1Column2Column3Column4Column5Column6Column7REGIONCountry CodeUIDEMP IDNAMEUSDColumn8Column9Column10Column11Column12
RegionCountryIDNameLCAmountUSD
RegionCountry CodeUIDEmp IDNameusd
na
1​
xyzzzzzztough
15​
na
1​
abcaaaaeasy
25​
RegionCountry CodeUIDEmp IDNameInput
emea
1​
xyzzzzzzgood
30​
emea
1​
abcaaaaokay
40
 

Watch MrExcel Video

Forum statistics

Threads
1,122,939
Messages
5,598,967
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top