Forum Discussion

MarkPalmberg's avatar
MarkPalmberg
Icon for Kudo Commander rankKudo Commander
4 years ago
Solved

Boolean 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...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    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's avatar
KNP
Icon for Super User rankSuper User
4 years ago

MarkPalmberg,

 

Shouldn't the second row be 'T' also, or am I misunderstanding the logic.

 

Try this:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjQyVtJRcnIEEkYGRgZKsTowMd9giJgRVMwQpA4iZgBVZwgWA2s2tDSHCpoYgTS7gMUsDJViYwE=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, DEGREE = _t, YEAR = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"ID", Int64.Type}, {"DEGREE", type text}, {"YEAR", Int64.Type}}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Restricted",
    each if [ID] = 123 then "T" else "F"
  ),
  #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"Restricted", type text}})
in
  #"Changed Type1"

Specifically, the 'Added Custom' step.