Forum Discussion

bitofanewb's avatar
bitofanewb
Frequent Visitor
1 year ago
Solved

Help with complex problem needing done in M, DAX or SQL

Hi,   I need to create a column called Current Pot, whose output says 'Yes' or 'No'.    I have 3 columns that can be used to help create this: TraineeID (unique number given to each trainee when ...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    GroupBy Trainee and Aggregate by Max of Pot

    Then Join with  ungrouped table and transform the non-empty tables to Yes

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijBU0lEyVIrVgTKNIEwjhKgRQtQYIWqMKmqMMMEYIWoCYZogtJmgWoGkzVQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trainee = _t, Pot = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trainee", type text}, {"Pot", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "URN", each [Trainee]&Number.ToText([Pot],"00"), type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Trainee"}, {{"Max Pot", each List.Max([Pot]), type nullable number}}),

    #"Merge" = Table.NestedJoin(#"Added Custom",{"Trainee","Pot"}, #"Grouped Rows",{"Trainee","Max Pot"},"Is Max Pot", JoinKind.LeftOuter),
    #"Is Max" = Table.TransformColumns(#"Merge",{"Is Max Pot", each if Table.IsEmpty(_) then null else "Yes", type nullable text})
in
    #"Is Max"

     

    Before

     

    After

     

     

     

ronrsnfld's avatar
ronrsnfld
Super User
1 year ago

GroupBy Trainee and Aggregate by Max of Pot

Then Join with  ungrouped table and transform the non-empty tables to Yes

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijBU0lEyVIrVgTKNIEwjhKgRQtQYIWqMKmqMMMEYIWoCYZogtJmgWoGkzVQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trainee = _t, Pot = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trainee", type text}, {"Pot", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "URN", each [Trainee]&Number.ToText([Pot],"00"), type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Trainee"}, {{"Max Pot", each List.Max([Pot]), type nullable number}}),

    #"Merge" = Table.NestedJoin(#"Added Custom",{"Trainee","Pot"}, #"Grouped Rows",{"Trainee","Max Pot"},"Is Max Pot", JoinKind.LeftOuter),
    #"Is Max" = Table.TransformColumns(#"Merge",{"Is Max Pot", each if Table.IsEmpty(_) then null else "Yes", type nullable text})
in
    #"Is Max"

 

Before

 

After

 

 

 

  • bitofanewb's avatar
    bitofanewb
    Frequent Visitor
    1 year ago

    Hey, 

     

    Thank you so much for your help. 

     

    With a couple of very minor tweaks it did the job:

     

    #"Grouped Rows" = Table.Group(#"Added Custom89", {"TRAINEEID"}, {{"Max Pot", each List.Max([Pot]), type nullable number}}),
    #"Merge" = Table.NestedJoin(#"Added Custom89",{"TRAINEEID","Pot"}, #"Grouped Rows",{"TRAINEEID","Max Pot"},"Is Max Pot", JoinKind.LeftOuter),
    #"Expanded Is Max Pot" = Table.ExpandTableColumn(Merge, "Is Max Pot", {"Max Pot"}, {"Max Pot"}),
    #"Added Custom92" = Table.AddColumn(#"Expanded Is Max Pot", "Current Pot", each if [Max Pot] = null then "No" else "Yes")
    in
    #"Added Custom92"

     

    Thanks again.