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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dphillips
Helper IV
Helper IV

Populate a column based on the latest boolean value

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.

 

YearSemesterIDYearLevelIBFlagFinalFlag
201211104215FALSE0
201221104215FALSE0
201311104216FALSE0
201321104216FALSE0
201411104217FALSE0
201421104217FALSE0
201511104218FALSE0
201521104218FALSE0
201531104218FALSE0
201541104218FALSE0
201551104218FALSE0
201591104218FALSE0

 

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.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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