I apprecicate that this must be a fairly simple issue to overcome however I have tried all join types with no success.

My data is structured in two excel files, one for 2022 and one for 2021. Headings are roughly the same on both :

ID    Name    2021 Quantity    2021 Assessment
1234  Name1   32               High
5678  Name2   9                Low
9112  Name3   1                Medium

and the same for 2022 :

ID    Name    2022 Quantity    2022 Assessment
3456  Name1   14               Medium
7891  Name3   23               Medium
1001  Name4   1                Low

I can join both sets on the NAME field, however the 2021 file will contain some Names that are not on the 2022 file, and vice versa. I am interested in the 2022 file as my primary source, and would like to show, in a table, all records and if there is a 2021 quantity (if not, show a blank). Output should look something like the below

ID     Name    2022 Quantity  2022 Assessment  2021 Quantity
1234   Name1   32             High             14
5678   Name2   9              Low 

I have experimented with one-to-many and many-to-many joins and various filters however every output seems to filter off the records where there is no join?

CodePudding user response：

The first option is to append the tables (in PowerQuery: Home Ribbon/Append queries). Before that, we need to rename column names, so that they have similar names, and add a year column to each table (in PowerQuery:Add Column/ Custom column) (please see the screenshot).

Then, we can just create a pivot table.

The second option is to create a table with unique Names and set relationships between that table and the original ones by the name columns. In PowerQuery:

1. Right-click on the 2021 table/Reference
2. Remove all columns except Names
3. Do the same for the 2022 table
4. Append those two tables as the new one
5. Remove duplicates in the new table

In PowerBI

1. Set relationships using the name columns
2. Create a pivot table