Representing multiples from two sets of tables

[Ry009]

Hi

I have two sets of data (with more columns than what my example shows) and wanted multiples represented. 

One table has positional data (once instance of each)

Position Number	Position Title	Position Start	Position End
5001	Admin Officer	1/06/2023	31/12/2023
5001	Admin Officer	1/06/2023	31/12/2023

Another table with employee data like this - where employees can hold the same position

Employee Number	First Name	Last Name	Position Number
1234	Person	A	5001
5678	Person	B	5001

What I am trying to do, is having the data represented like this, so that those holding the same position are represented in the one table.

Additionally, also so that if there are no employees holding a position, a null (or another custom text) can indicate a vacancies.

Position Number	Position Title	Position Start	Position End	Employee Number	First Name	Last Name
5001	Admin Officer	1/06/2023	31/12/2023	1234	Person	A
5001	Admin Officer	1/06/2023	31/12/2023	5678	Person	B
5002	Senior Admin Officer	01/01/2023	31/12/2023	null	null	null

I'm just not sure how to do this in Power BI?

[Ry009 · Accepted solution]

Hi 
I ended up getting there a similar way on my own

Right click - Edit Query 

Home Ribbon -  Merge Queries > Merge queries as new

Position Table - Select Positon # only

Selected Employee Table - Select Positon # only

Join Kind - Left Outer

Click OK - From here, scroll to the far right to the Employee table, then I was able to select/unselect the columns I was after.

[Ashish_Mathur]

Hi,

Create a relationship (Many to One and Single) from the Position number column of Table2 to the Position number column of Table1.  to your Table/matrix visual, drag all column from Table1 and first 3 from Table2.

Hope this helps. 

[Ry009]

Thanks for the quick reply. I've tried that before, however doesn't allow for nulls on Table 2. Need a left join type of approach if that makes sense.

[Ashish_Mathur]

You are welcome.  Instead of dragging columns from Table2, drag these measures

Measure = max('Table2'[Employee Number])

Measure 1 = max('Table2'[First Name])

Measure 2 = max('Table2'[Last Name])

Does this work?

[Ry009]

Hi Ashish

Think using max presents the same problem as it's going to pull in one result (one employee for one position) rather than 2 (that have the same position number).

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Table.NestedJoin(Positional, {"Position Number"}, Employee, {"Position Number"}, "Employee", JoinKind.LeftOuter),
    #"Expanded Employee" = Table.ExpandTableColumn(Source, "Employee", {"Employee Number", "First Name", "Last Name"}, {"Employee Number", "First Name", "Last Name"})
in
    #"Expanded Employee"

Both lines of Table are the same - that seems incorrect.  I changed that as per the image below