cross table query to find different grades for student

[Hatterg]

Greetings! I'd like some help with the following issue. 
We have three tables for storing students' grades across different moments. The three tables are the same, only the column "Period" has disctinctive data. The structure is similar to this: 

     

So, when a student fails period A1, s/he has the opportunity at period A2, and later at A3. is there a way to trace the students with failed grades and generate a new column with the final grade after the three periods? this is just an example, the real data is 1.3 million rows of data.

Note, for us a grade of 5 equals a Failed subject

Your help will be greatly appreciated! 

[v-nuoc-msft · Accepted solution]

Hi @Hatterg 

@Greg_Deckler Very interesting idea！

For your question, here is the method I provided:

Here's some dummy data

"PeriodA1"

"PeriodA2"

"PeriodA3"

Since your data is spread across three tables with similar structures, the first step is to merge these tables into one. This can be accomplished using Merge Query as New in Power Query.

Create a measure to calculation of final grades.

Final Grade = 
IF(
    SELECTEDVALUE(ConsolidatedGrades[PeriodA1]) > 5, 
    VALUES(ConsolidatedGrades[PeriodA1]), 
    IF(
        SELECTEDVALUE(ConsolidatedGrades[PeriodA2]) > 5, 
        VALUES(ConsolidatedGrades[PeriodA2]), 
        IF(
            SELECTEDVALUE(ConsolidatedGrades[PeriodA3]) > 5, 
            VALUES(ConsolidatedGrades[PeriodA3]), 
            BLANK()
        )
    )
)

Or you can create a calculated column.

Final Grades = 
IF(
    ConsolidatedGrades[PeriodA1] > 5, 
    ConsolidatedGrades[PeriodA1], 
    IF(
        ConsolidatedGrades[PeriodA2] > 5, 
       ConsolidatedGrades[PeriodA2], 
        IF(
            ConsolidatedGrades[PeriodA3] > 5, 
            ConsolidatedGrades[PeriodA3], 
            BLANK()
        )
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Hatterg Can you post that data as text so it can be copied and pasted into Enter data queries so that this can be mocked up? Also, is there any reason you can't combine those three tables into a single table using an Append query?

[Hatterg]

Thank's for your reply @Greg_Deckler . I can definitely append the tables, but after that I hit wall trying to obtain the final grade after the three periods thats why I thought about the possibility of a conditional column or something similar.

Sure, here is the data as text:

Id	Name	Last Name	Subject	Grade	Period
1	Carol	Danvers	Math	5	A1
2	Tony	Stark	Math	9	A1
3	Peter	Parker	Math	5	A1
1	Carol	Danvers	Language	6	A1
2	Tony	Stark	Language	8	A1
3	Peter	Parker	Language	5	A1
1	Carol	Danvers	Art	7	A1
2	Tony	Stark	Art	9	A1
3	Peter	Parker	Art	10	A1
1	Carol	Danvers	Science	5	A1
2	Tony	Stark	Science	7	A1
3	Peter	Parker	Science	9	A1

Id	Name	Last Name	Subject	Grade	Period
1	Carol	Danvers	Math	8	A2
3	Peter	Parker	Math	5	A2
3	Peter	Parker	Language	9	A2
1	Carol	Danvers	Science	5	A2

Id	Name	Last Name	Subject	Grade	Period
3	Peter	Parker	Math	8	A3
1	Carol	Danvers	Science	6	A3

[Ashish_Mathur]

Hi,

Based on the 3 tables that you have shared, show the expected result.