Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I am working with a list of clients who may have started 1, 2, 3 or more jobs, each of which has it's own job start date and job end date. Right now, each job appears in it's own row, meaning that the clients may appear in multiple rows if they have multiple jobs.
The goal is to have one row per client, with the columns being Client Name, Employment Name1, Job Start Date1, Job End Date1, Employment Name 2, Job Start Date2, Job End Date2, etc.
I have followed the steps here to Number Grouped Data as shown in the Job Number column above but I am stuck on what to do next. I have tried reading other posts and experimenting with pivoting/unpivoting/transposing but I have not been successful. Any help on next steps would be appreciated.
Solved! Go to Solution.
This is useful context. Note that you can sort by multiple columns though. You could sort primarily on client [Full Name] and then by [Job Start Date] secondarily (or tertiarily if you need [Employment Name] to be sorted secondarily).
In any case, I don't think pivoting your table will make life much easier.
The shape your data is already in is much easier to work with in Power BI. It's certainly possible to pivot the jobs into extra columns but I'd recommend against it if at all possible.
Can you share why you need to have one row per client? Are you wanting to use this as a dimension table?
Ultimately, I am needing to set it up to where I can add a calculated column that will calculate the difference between Job 1 End Date and Job 2 Start Date. The first way I tried to do this was to add an index column and make one row refer to the previous row - I have no idea if I am explaining this well, I am fairly new to PowerBI and followed a solution I found in another post. This worked, but since the rows didn't necessarily list the jobs in chronological order, then referring to the previous row wouldn't necessarily calculate the difference between Job 1 and Job 2 - it might be between Job 1 and Job 3 if they were out of order. And if I tried to sort the column by date so that the jobs would be in order, it would mix up the associated client names. I apologize if this is way too much info. I just want to find out the number of days between the end of one job and the start of the next and am struggling. The resulting number of days will then be used in a conditional column where if the number of days is greater than 30, the client is "Unemployed." I need this label of "Unemployed" for further calculations.
This is useful context. Note that you can sort by multiple columns though. You could sort primarily on client [Full Name] and then by [Job Start Date] secondarily (or tertiarily if you need [Employment Name] to be sorted secondarily).
In any case, I don't think pivoting your table will make life much easier.
Now that I look again at my Job Numbers column, the jobs are grouped by client name, but still not in chronological order. I might be approaching this from the wrong angle. I need to know if Client A had 30 days or more between the stop & start of any job - if so, Client A was "Unemployed."
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
12 | |
11 |