Forum Discussion
Transpose rows to column based on condition
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 a...
amitchandak
Super User
Super UserAnonymous , 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.
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