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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ry009
Helper II
Helper II

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 II
Helper II

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 II
Helper II

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors