March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
84 | |
76 | |
49 |
User | Count |
---|---|
163 | |
148 | |
103 | |
74 | |
55 |