Forum Discussion
Create a new collated table by combining queries listed in one table
Hi OrangeJuice
The current information is insufficient for further analysis. Please provide some dummy data to show the original table format you have. Please share it in table format so that we can use it for testing and tell whether it's necessary to do some data transformation or data modeling. Also share the logic to calculate the "Final Score" and "Weighted Total". Thank you.
Best Regards,
Jing
I just did below.
The Weighted Total and Final score should look at the Gradebook. It tells you which assignment is worth what % of the final grade. So if a student got 50% in their first assignment and it's worth 20% then weighted total is 10%. The final grade is just all those percentages added together. Please check my example files below, I've been pretty thorough.
The thing is though, I can do the logic once I can pull the data accross - but I can't figure out how to pull the data accross from the other tables using the strings that contain the names of the queries from the GradeBook table. I feel like I'm missing something simple, or it just can't be done?
- lbendlin
Super User
Your ID values do not match the file names. That makes it more difficult to create a better process
Looks like all the files have (very) different structure? That makes unpivoting them rather difficult.
Is Courseid_13731 the same as TRANS102-24S1 ?
I can use the file names if that works better? The grade book is manually generated. There are 2 types of structure, assignments and quizzes. I can duplicate the queries for those 2 set-ups and replace the sources, just need to figure out how to make the results table.
Hi OrangeJuice
Here is my outcome based on your PBIX file. It is a Matrix visual and it has a measure [Final Score] as Values field.
The generated table is expected to have the following format: it has TotalScore column and Assignment Name column. This is a user-friendly format for the DAX calculation. (I name the table as 'AllGrades' in the pbix file.)
Model relationships:
It takes a lot of words to explain all the steps, so I'll just introduce some key actions and recommendations below, which you can refer to the attachment for details.
- Make sure that all assignment tables have the columns we need, and that these columns have the same name and data type so that they can be appended based on column names correctly.
- Add [Assignment Name] column to each table before appending. It is recommended to add this column in Excel files so the template can be more dynamic for other courses.
- In 04_Translation, your original 'Total Score' column return scores larger than 100 ([TotalScore_1] in below image). I guess this is not correct so I modified it to get average score of all 5 scores ([TotalScore] in image).
- Append all assignment tables into a generated table and keep only necessary columns. If you want it to be more dynamic, it is recommended to use Power Query Folder connector. This requires all files share the same table format. You can take a file as Sample file and transform it. All files will be transformed in the same way and then combined together.
- Once transformation is ready, load data into Power BI Desktop.
- Modify the relationships as I have shown. Create two measures [Weighted Total] and [Final Score] which you can find in my PBIX file.
- Use a Matrix visual to show the outcome.
Let me know if you have any questions. Hope this will be helpful!
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
