Unpivot through M or ease

[Junaid11]

I have below dataset.

Student	Course1	Course2	Course3	Course1	Course2	Course1	Course2	Course3	Course1	Course2
James	01/02/2019		12/08/2000	Not Compliant	Not Compliant	Y	N	Y	01/02/2021
Jason	28/09/2020	24/09/2019	11/09/2015	Compliant	Not Compliant	N	Y	Y	28/09/2022	24/09/2021
John	17/05/2018	18/01/2017		Not Compliant	Not Compliant	N	N	N	17/05/2020	18/01/2019
Jenny	12/01/2021		08/09/2008	Not Compliant	Compliant	Y	Y	Y	12/01/2023

This data represents Student and courses they have done. First three course column shows courses and theie completeion date . Next two column shows Compliance of two courses. This goes on. I have done unpivot to see course name and their values in one column but my rows count has increased significantly. My columns have same course name but not all columns/course are repeating. Like course completion date is showing three course while compliance is showing for only two course. I want to see it as below outcome. 

James	Course1	01/02/2019	Not Compliant	Y	01/02/2021
James	Course2		Not Compliant	N
James	Course3	12/08/2000		Y
Jason	Course1	28/09/2020	Compliant	N	28/09/2022
Jason	Course2	24/09/2019	Not Compliant	Y	24/09/2021
Jason	Course3	11/09/2015		Y

Please help the way it can be done. Either M code or any other solution.

Thanks

[ryan_mayu · Accepted solution]

@Junaid11 

here is a wokaround for you . pls see the attachment below.

[amitchandak]

@Junaid11 , In the first table which has course number, add a prefix or a suffix to give meeting what it is

StartDate Course1

StartDate  Course2

StartDate Course3

Compliant Course1

Compliant Course2

Status Course1

Status Course2

Status Course3

EndDate Course1

EndDate Course2

Now unpivot all these columns. https://radacad.com/pivot-and-unpivot-with-power-bi

Split by Delimiter (first only) into 2 column https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

the pivot again using the first column in this split

refer this if needed

https://kohera.be/power-bi/how-to-unpivot-twice/