Help needed: Pivot for half of the the table

[SDVN]

Dear Experts,

Greetings! I am new for power BI and I was given a project to work on to practice but this data in excel seems little conplicated to handle in Power BI. 

The data that am using is as shown in the picture. I want to get the count of the Monthly count of "Trial" of 2023. But you can see that years (2023 and 2024) are not in a single coulmn. The years are mentioned in a merged cell about the months. So i have 2 sets of months, one for 2023 and another for 2024. How do I analyse the monthly data of each year? Can someone help? Please let me know if i have failed to mention any details. Thank you so much 🙂

If you want the sample data to try, do let me know. I can create a new one and share. 

Regards,

Sid 

[SDVN]

Thank you so much! I have just started to learn Power BI and there is so much to learn, with the support of kind experts like you, the learning journey wouldn't be very difficult. 🙂 Thank you for the help..

[DOLEARY85]

No problem, happy to help 🙂

[SDVN]

Sure! Thank you so much for helping 🙂 here is the data: 

https://docs.google.com/spreadsheets/d/1tqqj0_lbCIUPqBrJKSVlw7G-2zoPSb70/edit?usp=share_link&ouid=10...

Regards,

Sid

[DOLEARY85]

Hi,

if you have the sample data i'd be happy to take a look for you,

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[SDVN]

Sure! Thank you so much for helping :slightly_smiling_face: here is the data: https://docs.google.com/spreadsheets/d/1tqqj0_lbCIUPqBrJKSVlw7G-2zoPSb70/edit?usp=share_link&ouid=10... Regards, Sid

[DOLEARY85 · Accepted solution]

Yeah i can see where you're struggling, not the best data set to start off with,

could you remove the columns for 2024, promote the row headers and pivot the data in the month columns using power query?

[SDVN]

Okay.. can i do pivot for all the months columns? Can you help me how to do that? I could do only once (Unpivot), by selecting the "Jan" column but that did not help. Would you mind helping me understand a bit? 🙂 

[DOLEARY85]

No problem,

Just select all the month columns first before selecting unpivot