Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
A list of students are stored in a database. Every term, the students are rolled over to the next term so in the students table there will be mulitple rows for each student. For each row, the status of a student, called the IBFlag, is one of the fields. See table below. When the student's YearLevel = 11 the IBFlag may change to True. I want to created a column called FinalFlag which is 0 in every row for a student if their IBFlag is always false but, if the IBFlag becomes TRUE in Year 11 then every row for that student right back to when they were in their first year level is set to 1. This will allow me to set a filter of 1 with the FinalFlag and for all the data to show for a student, not just when he was in Year 11 and 12.
Year | Semester | ID | YearLevel | IBFlag | FinalFlag |
2012 | 1 | 110421 | 5 | FALSE | 0 |
2012 | 2 | 110421 | 5 | FALSE | 0 |
2013 | 1 | 110421 | 6 | FALSE | 0 |
2013 | 2 | 110421 | 6 | FALSE | 0 |
2014 | 1 | 110421 | 7 | FALSE | 0 |
2014 | 2 | 110421 | 7 | FALSE | 0 |
2015 | 1 | 110421 | 8 | FALSE | 0 |
2015 | 2 | 110421 | 8 | FALSE | 0 |
2015 | 3 | 110421 | 8 | FALSE | 0 |
2015 | 4 | 110421 | 8 | FALSE | 0 |
2015 | 5 | 110421 | 8 | FALSE | 0 |
2015 | 9 | 110421 | 8 | FALSE | 0 |
Here is a link to dta and what the FinalFlag column should look like
Any help on the best way to do this would be much appreciated.
Solved! Go to Solution.
Hi @dphillips
You can do this sort of thing by joining your table with itself on the ID column, then aggregating the joined table to get the max of the IBFlag boolean value per ID, and converting this value to a number.
Using your Google sheets source as an example, here is the M code. The important steps are highlighted in red.
I created these steps with the interface, with the #"Aggregate FinalFlag" step initially using a Count of IBFlag aggregation. Then I edited the code of that step to make the aggregation each Number.From(List.Max(_))
let Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/19_mi0giMiB55fGEnSjkXVqXgbxLZmrpSwb-MiIqDr78/export?format=xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Year", "Semester", "ID", "YearLevel", "IBFlag"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Year", Int64.Type}, {"Semester", Int64.Type}, {"ID", Int64.Type}, {"YearLevel", Int64.Type}, {"IBFlag", type logical}}), MergeWithSelfOnID = Table.NestedJoin(#"Changed Type",{"ID"},#"Changed Type",{"ID"},"JoinOnID",JoinKind.LeftOuter), #"Aggregate FinalFlag" = Table.AggregateTableColumn(MergeWithSelfOnID, "JoinOnID", {{"IBFlag", each Number.From(List.Max(_)), "FinalFlag"}}), #"FinalFlag Integer" = Table.TransformColumnTypes(#"Aggregate FinalFlag",{{"FinalFlag", Int64.Type}}) in #"FinalFlag Integer"
Regards,
Owen
Hi @dphillips
You can do this sort of thing by joining your table with itself on the ID column, then aggregating the joined table to get the max of the IBFlag boolean value per ID, and converting this value to a number.
Using your Google sheets source as an example, here is the M code. The important steps are highlighted in red.
I created these steps with the interface, with the #"Aggregate FinalFlag" step initially using a Count of IBFlag aggregation. Then I edited the code of that step to make the aggregation each Number.From(List.Max(_))
let Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/19_mi0giMiB55fGEnSjkXVqXgbxLZmrpSwb-MiIqDr78/export?format=xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Year", "Semester", "ID", "YearLevel", "IBFlag"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Year", Int64.Type}, {"Semester", Int64.Type}, {"ID", Int64.Type}, {"YearLevel", Int64.Type}, {"IBFlag", type logical}}), MergeWithSelfOnID = Table.NestedJoin(#"Changed Type",{"ID"},#"Changed Type",{"ID"},"JoinOnID",JoinKind.LeftOuter), #"Aggregate FinalFlag" = Table.AggregateTableColumn(MergeWithSelfOnID, "JoinOnID", {{"IBFlag", each Number.From(List.Max(_)), "FinalFlag"}}), #"FinalFlag Integer" = Table.TransformColumnTypes(#"Aggregate FinalFlag",{{"FinalFlag", Int64.Type}}) in #"FinalFlag Integer"
Regards,
Owen
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |