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:

IDDEGREEYEARRESTRICTED
123BA2020T
123MS2022F
121BS2000F
111BBA1970F
142MD1981F

 

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!

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"

7 Replies

  • freginier's avatar
    freginier
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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's avatar
      MarkPalmberg
      Icon for Kudo Commander rankKudo Commander
      4 years ago

      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.

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

     

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    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

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

      I should read properly to start with. ðŸ˜†

       

      This will work.

       

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

        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.