Remove blanks to put similar categories on same row

[EpicTriffid]

I have the data, pictured below, that I was to condense down so that the records with the same category (Column 1), are clumped together. You can see how each row is it's own record. So instead of:

Role	Dept 1 Name	Dept 1 Hours	Dept 2 Name	Dept 2 Hours
Admissions Tutor	Name 1	20
Admissions Tutor			Name 2	40
Admissions Tutor	Name 3	60
Admissions Tutor			Name 4	80

we have

Role	Dept 1 Name	Dept 1 Hours	Dept 2 Name	Dept 2 Hours
Admissions Tutor	Name 1	20	Name 2	40
Admissions Tutor	Name 2	40	Name 4	80

Any ideas?

This is what my data currently looks like:

[Anonymous]

Hi @EpicTriffid ,

If your idea is like what I said before, then I'm sorry, this feature is not available at the moment.
But I can offer you another solution. You can use DAX to create a new table and extract the different data separately according to the Dept n Name classification:

Dept 1 Name = 
FILTER('Table','Table'[Dept 1 Name] <> BLANK())

Dept 2 Name = 
FILTER('Table','Table'[Dept 2 Name] <> BLANK())

The results are shown below:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @EpicTriffid 

I'm guessing you wish to put this form:

Convert to this table:

But over here maybe you wrote it wrong?

It should be Name3 60?

Best Regard,
Dino Tao

[AnalyticPulse]

you can maintain single column for the department and reocrd department 1 and 2 in that column. same for the hours and name column. this will remove nulls from your data.

If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/

subscribe to Youtube channel For fun facts:
https://www.youtube.com/@CogniJourney

[EpicTriffid]

Hi @AnalyticPulse 

I'm not entirely sure what you mean?

[AnalyticPulse]

keep yout data in below format for above table 

Role    DeptName   dept Hour

im confused why there are two column for same kind of attribute. like if you can maintain departments in same column. not sure what you want to achive here.