Forum Discussion
Sum of column
I have two tables: "Comparison" and "Tests".
The "Comparison" table contains information about semesters (column "Semester") and school years (column "School Year"). Meanwhile, the "Tests" table contains information about students' test results, including semesters and school years.
Problem:
I would like to add a new column to the "Comparison" table called "Sum students", which will contain the sum of the number of students from the "Tests" table depending on the values in the "Semester" and "School Year" columns in the "Comparison" table.
Example:
If in the "Comparison" table I have an entry "Semester: Winter", "School Year: 2021/2022", then I would like the "Sum students" column to contain the sum of the number of students from the "Tests" table who took tests in the "Winter" semester of the school year "2021/2022".
Request for help:
Could someone please advise me on how to accomplish this task in Power Query? I would like to create the appropriate formula or step that will allow me to add this new column to the "Comparison" table.
Thank you in advance for any help!
Hi agggga
According to the description, you can use the Merge queries feature in Power Query to get the expected result you want.
Merge queries overview - Power Query | Microsoft Learn
Left outer join - Power Query | Microsoft Learn
You can select the "Semester" and "School Year" columns in both tables to perform the left outer join by selecting Ctrl as you select the columns. After merging the "Tests" table to "Comparison" table, you will have a new column called "Tests" in "Comparison" table. This new column has all matching results in tables.
Then click the Expand icon on column header of "Tests" column, switch to "Aggregate" option and select Sum to aggregate the "number of student" column. Or you can select other aggregation types per your need.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
2 Replies
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/1447523Hi agggga
According to the description, you can use the Merge queries feature in Power Query to get the expected result you want.
Merge queries overview - Power Query | Microsoft Learn
Left outer join - Power Query | Microsoft Learn
You can select the "Semester" and "School Year" columns in both tables to perform the left outer join by selecting Ctrl as you select the columns. After merging the "Tests" table to "Comparison" table, you will have a new column called "Tests" in "Comparison" table. This new column has all matching results in tables.
Then click the Expand icon on column header of "Tests" column, switch to "Aggregate" option and select Sum to aggregate the "number of student" column. Or you can select other aggregation types per your need.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
