Combine data in three separate data sources

[Anonymous]

Hi

I have three separate excel files, with some duplicated columns and some unique; the rows may appear in one, two or all three files (in total there are >200 rows / about 80 are unique).  For reference the column headers for each sheet are shown below.

I would like to create a table (???) showing ALL columns from the three files and to merge the information;  but to not duplicate the rows (e.g. a row that appears on both Sheet 1 and Sheet 3 would contain the same information in the first five columns - but the 'Revenue 3' column needs joining with the 'Date' cells in Sheet 1) - the aim is to have all data showing for a particular reference.  I'd appreciate your help - I have been going round in circles with this one 😉

Header 1

Header 2

Header 3

Header 4

Header 5

Header 6

Header 7

Header 8

Header 9

Header 10

Header 11

Header 12

Sheet 1

Account

Ref.

Name

Sales

Revenue 1

Date 1

Date 2

Date 3

Date 4

Date 5

Sheet 2

Account

Ref.

Name

Sales

Revenue 1

Revenue 2

Date 1

Date 2

Date 3

Date 4

Date 5

Date 6

Sheet 3

Account

Ref.

Name

Sales

Revenue 1

Revenue 3

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could use Append and Merge function in Power BI to combine the different data tables.

Please refer to this blog:

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Regards,

Lin