Aug 09 2021 02:22 PM
So suppose, in an organization, multiple team members are working on multiple projects. For some reason, whoever entered the data did it in the following way shown in the below screenshot.
We can imagine there are thousand of people on the list. For the simplicity's sake, I've put just two people. So Kelly's been working on three projects D E F with all different starting dates, exit dates, and reasons for exit. Mike has been working on projects A, B, C, and so on.
The thing is, Mike hasn't exited from project B, so the table above only shows start/exit info for projects A and C only.
I would like to split those columns into multiple rows, while matching all the relevant info (especially for Mike's project A and C data).
So far, using Power Query, I was able to do the following (SplitText in the list for each column I want to split, then create TableFromColumns),
and then expanding the nested table, finally I got
For Kelly, this is what I'm looking for. But for Mike, as you can see, there's a mismatch because of the missing data for project B. As you can expect, at the end of the day what I want is something like the following:
where Mike's row on B project shows an empty cell for the Exit Date column and Reason column because he's still on that project.
Is there anyway I can transform the original sheet into what I want? Thank you!
Aug 14 2021 03:17 AM
You need to identify projects for start and end. As variant
- keep table with created lists as it is now
- reference it and extract projects for start
- reference again, zip end with reason and extract projects for them
- merge above on ID and Project
Please check details in attached.