Boolean column if an ID has specified value in any row?

[MarkPalmberg]

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
142	MD	1981	F

I'd like to generate a column in Power Query that returns T for all rows associated with ID 123. The business logic is that anyone with any restricted degree should show up as being restricted.

Thanks!

[AlexisOlson · Accepted solution]

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"

[MarkPalmberg]

Ah, that's it. Thanks so much, @AlexisOlson . I really appreciate your time. 

[KNP]

Ah, yep. After seeing @AlexisOlson's reply I understand the logic better.

I should read properly to start with. 😆

This will work.

[MarkPalmberg]

Sorry, @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.

[KNP]

@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.

[freginier]

You can easily do this in DAX 

Column = 
var MinYear = 
CALCULATE( 
    MIN('Table'[Year]), 
    FILTER('Table',EARLIER('Table'[ID])='Table'[ID])
)
return 
LOOKUPVALUE('Table'[Rest],'Table'[Year],MinYear)

The result

[MarkPalmberg]

Thanks for the reply, @freginier . I'm trying to derive this field in Power Query because I'm creating a table in a dataflow, so it hasn't hit a .pbix at this point. Guess maybe I should just do it in SQL as part of the initial table load.