Create a new collated table by combining queries listed in one 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!