Hi,
I need guideline for data analysis from individual subject result tables. Attached two tables are from different courses. Course-1 has different question headers from Course-2. Also, there are many dozens courses for different semesters. Some are core courses while a few are electives.
Issue: Currently, I am thinking to deal the issue with merging of tables. Is there any method where I may be able to combine these tables with less effort? The second thought is to first unpivot the columns with headers like {Sr No, Name, RegNo,…, Questions and Marks} and then append different course tables. But technically, It might be wrong method.
As data in excel files for each course having many tables (Course-info, Course Mapping, Student IDs, Marks Sheets, Attendance Sheets etc) are being collected in SharePoint Folder.
Depending on what do you mean with “combine these tables with less effort”, having each Excel file containing more sheets (Course-info, Course Mapping, etc), probably you will have to combine them per “category” - meaning in fact per sheet.
So, I suppose that using the “import from folder” option of Power Query you could get all Excel files, but you have to create separate queries for each worksheet to be able to relate them in the data model.
And also you may need to unpivot data, especially for the tables you have presented above, but just the columns from MidQ1 to the right side. More exactly preserve RegNo till Program and “unpivot other columns”.
Also, don’t forget to preserve the column(s) that will help you preserver the course name or whatever will help you calculate the grade of a student for that course.
The dimension tables are simple and I think by appending unique data, these will serve the purpose. The sheets like (Course-info, Course Mapping, etc) can easily be appended. A composite key [2k18|Fall|7th |MS-404|2021] has been generated for each class and course result for these tables. From SharePoint folder, I may call these in Power Query and append each category table.
For individual course, I developed a Teacher Dashboard.
Now, I think that as all teachers are submitting their course files on SharePoint folder, so, I should fetch data to develop a comprehensive model. For Fact Table, Can I make a query with all possible headings {as teachers would differ the result by different assessment tools and frequency. Some teachers would test 5 Mid Exam while others may assess 7 Mid-Exam questions. The Master Sheet can be as Sample File EDNA-2.xlsm (67.8 KB)
By using this approach, I may be able to append data from different courses for same class or new class and also for different subjects or should I merge different course results instead of appending. I have over 700 students in 4 sessions and around 70 courses for each session. Also, Is it possible to develop a table with all students in Master Sheet (all headings) and append the data first and then unpivot at second step to transform it in the fact table.
I apologize as I feel that, perhaps, I am not expressing well.
I didn’t quite understand what is your question or concern, but if my explanation is not what you expected, please try give a more detailed (but short) example.
One of your concerns was related to a query will “all possible headers”. This could be done by directly appending tables.
For example I you have 2 tables with only a part of the columns common:
Now, if you select the Name column and “Unpivot Others” you will get an Attribute/Value pair for the questions or whatever you have on that columns.
Based on this unpivoted table you could do the needed calculation or use it to recreate the individual reports, eventually filtering based on some criteria.
Hi @Tanzeel, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.
