Forum Discussion
Combine multiple rows into one row
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."