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 | 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
3 Replies
Anonymous , 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
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
