Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
here is a wokaround for you . pls see the attachment below.
Proud to be a Super User!
here is a wokaround for you . pls see the attachment below.
Proud to be a Super User!
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |