How to combine multiple sheets from an Excel Workbook when the Headers are different

[Anonymous]

I have an Excel source file that has multiple Sheets Split by Year that I wish to combine into one query

i.e.

The first sheet has 12 columns 

Starting 1/10/19 - 1/11/19 - 1/12/19 etc to 1/9/20

The second sheet has 12 columns 

Starting 1/10/20 - 1/11/20 - 1/12/20 etc to 1/9/21

The first four columns in each sheet are the same i.e

Region

Customer

Delivery Model

Contract End Date

[Anonymous]

It is not at all clear what you mean by the term combine.

There are several functions in Power Query that use this term: table.combine, List.Combine, reocrd.Combine, text.combine and maybe more ...

but maybe in your case, with a lot of imagination, what you need is the merge function between tables.

but if you don't provide enough information, I'm afraid you won't get any useful help ...

ciao

[Anonymous]

Rocco,

Thank you for your answer

Below are the top few lines of each sheet with customer names set to dummy ones

You will notice that only the headers are different on the date side

FY2020

2021

[Anonymous]

Ok.

With tables as images it is difficult (impossible for me) to work. I try to tell you verbally what should be done. If I understand correctly, you want to have the 2020 and 2021 data in the same table side by side. For this you should use the table.join function. If you also provide fake tables but they can be copied, I can show you how

[Anonymous]

Rocco

Thank you

Yes I want one query so the visuals can be filtered by year

How can I send a fake workbook so you can help me with table.join function?

Much appreciated

Gary

[Anonymous · Accepted solution]

you could load the file on some free file sharing sites.

but perphs some other could give a better advice

[Anonymous]

Rocco,

Thanks for your guidance, with that and Google I have got it to work using Merge 🙂

Gary