Transpose rows to column based on condition

[avarghese]

Hi,

I am trying to find a way transpose the row short_name which contains the values MAIM and  AIM into two columns. Is this possbile to be done in Power BI ? 

SchoolId	Value	AspectId	aspect	aspect_desc	short_name
070557001540	No	43	Communication and Language	EYFS Tracker	CL
070557001540	Yes	62	Expressive Art and Design	EYFS Tracker	EAD
070557001540	Yes	45	Literacy	EYFS Tracker	L
070557001540	Yes	46	Maths	EYFS Tracker	M
070557001540	54	41	Milestone Age in Months	EYFS Tracker	MAIM
070814115383	Yes	44	Physical Development	EYFS Tracker	PD
070814115383	Yes	42	Personal, Social and Emotional Development	EYFS Tracker	PSED
070814115383	No	61	Understanding the World	EYFS Tracker

After transpose it should look like the below

SchoolId	Value	AspectId	aspect	short_name	Milestone Age
070557001540	No	43	Communication and Language	CL	MAIM
070557001540	Yes	62	Expressive Art and Design	EAD	MAIM
070557001540	Yes	45	Literacy	L	MAIM
070557001540	Yes	46	Maths	M	MAIM
070557001540	Yes	44	Physical Development	PD	MAIM
070557001540	Yes	42	Personal, Social and Emotional Development	PSED	MAIM
070557001540	Yes	61	Understanding the World	UTW	MAIM
070814115383	No	43	Communication and Language	CL	MAIM
070814115383	Yes	62	Expressive Art and Design	EAD	MAIM
070814115383	No	45	Literacy	L	MAIM
070814115383	Yes	46	Maths	M	MAIM
070814115383	Yes	44	Physical Development	PD	MAIM
070814115383	Yes	42	Personal, Social and Emotional Development	PSED	MAIM
070814115383	No	61	Understanding the World	UTW	MAIM

Thanks

Best regards,

abraham

[avarghese]

Hello Amit,

Sorry for the late response. I got busy with some blackout issues. My original data is as below and I need to produce a report as shown the below format. 

Orignal Data

SchoolId	Value	AspectId	aspect	aspect_desc	short_name	group_name	gradesetid
070557001540	54	41	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
070557001540	No	43	Communication and Language	EYFS Tracker	CL	Baseline	4
070557001540	Yes	42	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
070557001540	Yes	44	Physical Development	EYFS Tracker	PD	Baseline	4
070557001540	Yes	45	Literacy	EYFS Tracker	L	Baseline	4
070557001540	Yes	46	Maths	EYFS Tracker	M	Baseline	4
070557001540	Yes	61	Understanding the World	EYFS Tracker	UTW	Baseline	4
070557001540	Yes	62	Expressive Art and Design	EYFS Tracker	EAD	Baseline	4
070814115383	54	41	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
070814115383	No	43	Communication and Language	EYFS Tracker	CL	Baseline	4
070814115383	No	45	Literacy	EYFS Tracker	L	Baseline	4
070814115383	No	61	Understanding the World	EYFS Tracker	UTW	Baseline	4
070814115383	Yes	42	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
070814115383	Yes	44	Physical Development	EYFS Tracker	PD	Baseline	4
070814115383	Yes	46	Maths	EYFS Tracker	M	Baseline	4
070814115383	Yes	62	Expressive Art and Design	EYFS Tracker	EAD	Baseline	4
081103508616	36	22	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
081103508616	No	14	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
081103508616	No	24	Literacy	EYFS Tracker	L	Baseline	4
081103508616	Yes	16	Communication and Language	EYFS Tracker	CL	Baseline	4
081103508616	Yes	23	Physical Development	EYFS Tracker	PD	Baseline	4
081103508616	Yes	25	Maths	EYFS Tracker	M	Baseline	4
083331119453	42	22	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
083331119453	No	14	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
083331119453	No	16	Communication and Language	EYFS Tracker	CL	Baseline	4
083331119453	No	24	Literacy	EYFS Tracker	L	Baseline	4
083331119453	No	25	Maths	EYFS Tracker	M	Baseline	4
083331119453	Yes	23	Physical Development	EYFS Tracker	PD	Baseline	4
083347641877	42	22	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
083347641877	No	14	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
083347641877	No	23	Physical Development	EYFS Tracker	PD	Baseline	4
083347641877	No	24	Literacy	EYFS Tracker	L	Baseline	4
083347641877	Yes	16	Communication and Language	EYFS Tracker	CL	Baseline	4
083347641877	Yes	25	Maths	EYFS Tracker	M	Baseline	4

final output

	% Yes	All Yes	All No
MAIM	PSED	CL	PD	L	M	% All Yes	% All No
36
42
54
All

Hope this clarifies the query. Is this possible to be achieved in Power BI ?

Thanks.

Best regards,

Abraham

[amitchandak]

@avarghese , what logic of table 2, I am not able to get.

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[avarghese]

Hello Amit,

Sorry for the late response. I got busy with some blackout issues. My original data is as below and I need to produce a report as shown the below format. 

Orignal Data

SchoolId	Value	AspectId	aspect	aspect_desc	short_name	group_name	gradesetid
070557001540	54	41	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
070557001540	No	43	Communication and Language	EYFS Tracker	CL	Baseline	4
070557001540	Yes	42	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
070557001540	Yes	44	Physical Development	EYFS Tracker	PD	Baseline	4
070557001540	Yes	45	Literacy	EYFS Tracker	L	Baseline	4
070557001540	Yes	46	Maths	EYFS Tracker	M	Baseline	4
070557001540	Yes	61	Understanding the World	EYFS Tracker	UTW	Baseline	4
070557001540	Yes	62	Expressive Art and Design	EYFS Tracker	EAD	Baseline	4
070814115383	54	41	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
070814115383	No	43	Communication and Language	EYFS Tracker	CL	Baseline	4
070814115383	No	45	Literacy	EYFS Tracker	L	Baseline	4
070814115383	No	61	Understanding the World	EYFS Tracker	UTW	Baseline	4
070814115383	Yes	42	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
070814115383	Yes	44	Physical Development	EYFS Tracker	PD	Baseline	4
070814115383	Yes	46	Maths	EYFS Tracker	M	Baseline	4
070814115383	Yes	62	Expressive Art and Design	EYFS Tracker	EAD	Baseline	4
081103508616	36	22	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
081103508616	No	14	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
081103508616	No	24	Literacy	EYFS Tracker	L	Baseline	4
081103508616	Yes	16	Communication and Language	EYFS Tracker	CL	Baseline	4
081103508616	Yes	23	Physical Development	EYFS Tracker	PD	Baseline	4
081103508616	Yes	25	Maths	EYFS Tracker	M	Baseline	4
083331119453	42	22	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
083331119453	No	14	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
083331119453	No	16	Communication and Language	EYFS Tracker	CL	Baseline	4
083331119453	No	24	Literacy	EYFS Tracker	L	Baseline	4
083331119453	No	25	Maths	EYFS Tracker	M	Baseline	4
083331119453	Yes	23	Physical Development	EYFS Tracker	PD	Baseline	4
083347641877	42	22	Milestone Age in Months	EYFS Tracker	MAIM	Baseline	5
083347641877	No	14	Personal, Social and Emotional Development	EYFS Tracker	PSED	Baseline	4
083347641877	No	23	Physical Development	EYFS Tracker	PD	Baseline	4
083347641877	No	24	Literacy	EYFS Tracker	L	Baseline	4
083347641877	Yes	16	Communication and Language	EYFS Tracker	CL	Baseline	4
083347641877	Yes	25	Maths	EYFS Tracker	M	Baseline	4

final output

	% Yes	All Yes	All No
MAIM	PSED	CL	PD	L	M	% All Yes	% All No
36
42
54
All

Hope this clarifies the query. Is this possible to be achieved in Power BI ?

Thanks.

Best regards,

Abraham