Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Ry009
Helper I
Helper I

Representing multiples from two sets of tables

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 NumberPosition TitlePosition StartPosition End
5001Admin Officer1/06/202331/12/2023
5001Admin Officer1/06/202331/12/2023

 

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

 

Employee NumberFirst NameLast NamePosition Number
1234Person A5001
5678Person B5001



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 NumberPosition TitlePosition StartPosition EndEmployee NumberFirst NameLast Name
5001Admin Officer1/06/202331/12/20231234Person A
5001Admin Officer1/06/202331/12/20235678Person B
5002Senior Admin Officer01/01/202331/12/2023nullnullnull


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


1 ACCEPTED SOLUTION
Ry009
Helper I
Helper I

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.
2024-03-04_08-23-40.png

View solution in original post

6 REPLIES 6
Ry009
Helper I
Helper I

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.
2024-03-04_08-23-40.png

Ashish_Mathur
Super User
Super User

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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).

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

Ashish_Mathur_0-1708927567655.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.