Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
agggga
New Member

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!

1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

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. 

vjingzhanmsft_0-1707722754775.png

 

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. 

vjingzhanmsft_1-1707722969946.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

2 REPLIES 2
v-jingzhan-msft
Community Support
Community Support

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. 

vjingzhanmsft_0-1707722754775.png

 

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. 

vjingzhanmsft_1-1707722969946.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

lbendlin
Super User
Super User

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-...

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/1447...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors