Combine Two excel files with different column names and different amount of columns

[Anonymous]

Currently, I am receiving excel files from two different sources. I want to combine them for analysis on Power BI. However, because these reports are generated differently, the column/header names in the sheets are completely different and not all data would be present in both reports. For example, 

A

Account Manager                  Location                      Account Number               Sales

Jay                                          New York                                  1                         100000

B

Location Name                      Sales                                  Date                      Notes                Manager 

Philadelphia                            200000                             10/23/2019           improved          Adam

Account Manager=Manager, Location=Location Name, Sales=Sales

Date, Account Number, Notes are all unique categories 

I want a final dataset with the two reports combined and then the missing values would just be null/empty. 

Something like this:

How would I get the above submitted in as the data in order to create reports on Power BI?

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can use try to use combine function to achieve your requirement:

let
    Source = Table.Combine({T1, Table.RenameColumns(T2,{{"Manager","Account Manager"},{"Location Name","Location"}})})
in
    Source

Regards,

Xiaoxin Sheng

[labuser1235]

You can do it easily in power bi edit queries. 

Change the column names in table 2 as per your requirement and re-arrange the order of the columns. 

After that, in Edit queries --> Home --> Click on Append Queries. Click on two tables and select table1. 

You will get your desired output then click on close and apply.