Forum Discussion
MarkPalmberg
Kudo Commander
4 years agoBoolean column if an ID has specified value in any row?
Hi. I have a table of graduates and the degrees they've earned. Like so: ID DEGREE YEAR RESTRICTED 123 BA 2020 T 123 MS 2022 F 121 BS 2000 F 111 BBA 1970 F 1...
- 4 years ago
You can group by ID taking the max over RESTRICTED and then merge that back with the original table like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcnIEEkYGRgZAKkQpVgcm7hsMETcCUm5QcUOQeoi4gQFC3BAsDjbI0NIcScIEpNnXBSxuYQgRjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DEGREE = _t, YEAR = _t, RESTRICTED = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DEGREE", type text}, {"YEAR", Int64.Type}, {"RESTRICTED", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AnyRestricted", each List.Max([RESTRICTED]), type nullable text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Grouped Rows", {"ID"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"AnyRestricted"}, {"AnyRestricted"}) in #"Expanded Grouped Rows"
KNP
Super User
4 years agoAh, yep. After seeing AlexisOlson's reply I understand the logic better.
I should read properly to start with. 😆
This will work.
MarkPalmberg
Kudo Commander
4 years agoSorry, KNP , that was my bad. I should've included my desired result column in that table. Thanks so much for taking the time to look at this.