Forum Discussion
Create a new collated table by combining queries listed in one table
I've got a PowerBI model which I'd like to use for a variety of courses I teach.
Each of these courses has several assignments, and the total number of assignments, their names and weightings change from course to course.
I'm trying to make this PowerBI project generate as much as possible dynamically to avoid having to do a lot of work to it for every new course, I jut want to replace the old course's assessment tables (plus or minus depending on the difference between the two) and have it all just work dynamically.
To this effect, I set up an excel file with a table called "GradeBook" which lists each table containing assessment grades and the weightings of those assessments and loaded it into PowerBI.
I'd like to be able to create a dynamic table which gives me the assessment grades for each participant in the course. It should look up the "Username" column values in the "Participants" table to give me a list of students, and then add a column for each of the "TotalScore" values in the variety of assessment tables I have imported.
Finally it should have a column called "Final Score" which gives me the total score for the course.
So the end result should be something like:
| Username | Assignment 1 | Assignment 2 | Assignment 3 | Assignment 4 | Assignment 5 | Weighted Total |
| 12345AB | 80 | 90 | 80 | 7 | 9 | 83% |
| 67890CD | 70 | 69 | 58 | 5 | 5 | 67% |
Can anyone advise how to do this either in Dax or M so that the number of assignments (columns) can change and this can all be recalculated?
7 Replies
- lbendlin
Super User
Sounds like the Power BI user interface is a better place for this (maybe with a little help from DAX).
Consider using Field Parameters for column selections.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523I've uploaded it to my Google Drive. In the example I have created a query in the PowerQuery Editor that makes a table called Calculated Grades_Generated. I've managed to pull the correct headings but can't figure out how to populate it. It's important that all the data is dynamically generated because I want to use this as a template for courses that have a variety of assignments.
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,
JingI 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
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 ?
