Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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.
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.
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.
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?
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |