If in a group of

If in a group of IDs, first occurrence of event Z appears with a category of Temporary, then all Values should be Yes otherwise No. In 315 – First occurrence of event Z doesn’t have a category of Temporary, hence all values will be No. In 401 – First occurrence of event Z has a category of Temporary, hence all values will be Yes.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 25
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of If in a group of with Power Query

Power Query solution 1 for If in a group of, proposed by Luan Rodrigues:

let
  Fonte = Data, 
  Index = Table.AddIndexColumn(Fonte, "Índice", 0, 1, Int64.Type), 
  Group = Table.Group(
    Index, 
    {"ID"}, 
    {
      {
        "Contagem", 
        each Table.AddColumn(
          Table.AddColumn(
            Table.AddIndexColumn(
              Table.Sort(_, {{"Event", Order.Descending}, {"Índice", Order.Ascending}}), 
              "Rank", 
              0, 
              1
            ), 
            "Personalizar", 
            each if [Event] = "Z" and [Category] = "Temporary" and [Rank] = 0 then "Yes" else "No"
          ), 
          "Value", 
          each if [Rank] = 0 then [Personalizar] else null
        ), 
        type table [
          Event = nullable text, 
          Category = nullable text, 
          Value = nullable text, 
          Índice = nullable number
        ]
      }
    }
  )[[Contagem]], 
  Exp = Table.FillDown(
    Table.ExpandTableColumn(
      Group, 
      "Contagem", 
      Table.ColumnNames(Group[Contagem]{0}), 
      Table.ColumnNames(Group[Contagem]{0})
    ), 
    {"Value"}
  ), 
  Result = Table.Sort(Exp, {{"Índice", Order.Ascending}})[[ID], [Event], [Category], [Value]]
in
  Result

Power Query solution 2 for If in a group of, proposed by Brian Julius:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjY0VdJRigDikNTcgvyixKJKpVgdmHgkECNxozC5qLpMDAyhpiFxcSkDGR6QWpSbmJeaV4KmHlXc1NgEqyMh4pjmm5pbYDUfIo7FfLg4kjmxAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, Event = _t, Category = _t]
  ), 
  Group = Table.Group(
    Source, 
    {"ID", "Event"}, 
    {
      {
        "All", 
        each _, 
        type table [ID = nullable text, Event = nullable text, Category = nullable text]
      }
    }
  ), 
  Dupe = Table.DuplicateColumn(Group, "All", "All2"), 
  ZRows = Table.SelectRows(Dupe, each _[Event] = "Z"), 
  ZRowsIdx = Table.AddColumn(ZRows, "ZRowsIndex", each Table.AddIndexColumn([All2], "Index", 1, 1)), 
  Z1Cat = Table.AddColumn(
    ZRowsIdx, 
    "Value", 
    each Table.SelectRows([ZRowsIndex], each _[Index] = 1)[Category]
  ), 
  Extract = Table.RemoveColumns(
    Table.TransformColumns(
      Z1Cat, 
      {"Value", each Text.Combine(List.Transform(_, Text.From)), type text}
    ), 
    {"All2", "ZRowsIndex", "Event", "All"}
  ), 
  YN = Table.TransformColumns(Extract, {"Value", each if _ = "Temporary" then "Yes" else "No"}), 
  Final = Table.Join(Source, "ID", YN, "ID")
in
  Final

Power Query solution 3 for If in a group of, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"ID", Int64.Type}, {"Event", type text}, {"Category", type text}}
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Event] = "Z")), 
  #"Grouped Rows" = Table.Group(
    #"Filtered Rows", 
    {"ID"}, 
    {
      {
        "All", 
        each _, 
        type table [ID = nullable number, Event = nullable text, Category = nullable text]
      }
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows", 
    "Value", 
    each if Table.First([All])[Category] = "Temporary" then "Yes" else "No"
  ), 
  #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"All", "Value"}), 
  #"Expanded All" = Table.ExpandTableColumn(
    #"Removed Other Columns", 
    "All", 
    {"ID", "Event", "Category"}, 
    {"ID", "Event", "Category"}
  ), 
  Back = #"Changed Type", 
  Custom1 = Table.NestedJoin(Back, {"ID"}, #"Expanded All", {"ID"}, "Back", JoinKind.LeftOuter), 
  #"Expanded Back" = Table.ExpandTableColumn(Custom1, "Back", {"Value"}, {"Value"}), 
  #"Removed Duplicates" = Table.Distinct(#"Expanded Back")
in
  #"Removed Duplicates"

Power Query solution 4 for If in a group of, proposed by Matthias Friedmann:

let
 Source = Excel.CurrentWorkbook(){[Name = "Ztemporary"]}[Content], 
 #"Grouped Rows" = Table.Group(
 Source, 
 {"ID"}, 
 {
 {"All", each _}, 
 {
 "Value", 
 each not Table.IsEmpty(
 Table.SelectRows(
 Table.Distinct(_, {"Event"}), 
 each [Event] = "Z" and [Category] = "Temporary"
 )
 )
 }
 }
 ), 
 #"Expanded All" = Table.ExpandTableColumn(
 #"Grouped Rows", 
 "All", 
 {"Event", "Category"}
 )
in
 #"Expanded All"

Explanations and a comparison to an alternative approach:
https://www.linkedin.com/posts/matthiasfriedmann_powerquerychallenge-daxchallenge-m-activity-6997232373448769536-UDt8

Power Query solution 5 for If in a group of, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Grouped = Table.Group(
    Source, 
    {"ID"}, 
    {
      {
        "all", 
        each 
          let
            t = _, 
            b = Table.SelectRows(t, each [Event] = "Z")[Category]{0} = "Temporary"
          in
            Table.AddColumn(t, "Value", each if b then "Yes" else "No")
      }
    }
  ), 
  Combine = Table.Combine(Grouped[all])
in
  Combine

Power Query solution 6 for If in a group of, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped Rows" = Table.Group(
    Source, 
    {"ID", "Event"}, 
    {{"Count", each if _[Category]{0} = "Temporary" then "Yes" else "No"}}
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Event] = "Z")), 
  #"Merged Queries" = Table.NestedJoin(
    Source, 
    {"ID"}, 
    #"Filtered Rows", 
    {"ID"}, 
    "Filtered Rows", 
    JoinKind.LeftOuter
  ), 
  #"Expanded Filtered Rows" = Table.ExpandTableColumn(
    #"Merged Queries", 
    "Filtered Rows", 
    {"Count"}, 
    {"Value"}
  )
in
  #"Expanded Filtered Rows"

Power Query solution 7 for If in a group of, proposed by Dominic Walsh:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Filter = Table.SelectRows(Source, each ([Event] = "Z")), 
  Group = Table.Group(
    Filter, 
    {"ID"}, 
    {{"Count", each _, type table [ID = number, Event = text, Category = nullable text]}}
  ), 
  Row0 = Table.AddColumn(Group, "Custom", each [Count]{0}), 
  Expand = Table.ExpandRecordColumn(Row0, "Custom", {"Category"}, {"Category"}), 
  YesNo = Table.AddColumn(Expand, "Value", each if [Category] = "Temporary" then "Yes" else "No"), 
  Remove = Table.RemoveColumns(YesNo, {"Count", "Category"}), 
  Join = Table.Join(Source, "ID", Remove, "ID")
in
  Join

Power Query solution 8 for If in a group of, proposed by Gabriel Gordon:

let
  Tbl1 = Excel.CurrentWorkbook(){[Name = "tblId"]}[Content], 
  Tbl2 = Excel.CurrentWorkbook(){[Name = "tblId"]}[Content], 
  OnlyZ = Table.SelectRows(Tbl2, each ([Event] = "Z")), 
  GroupID = Table.Group(
    OnlyZ, 
    {"ID"}, 
    {
      {
        "tblZ", 
        each _, 
        type table [ID = nullable number, Event = nullable text, Category = nullable text]
      }
    }
  ), 
  AddedValue = Table.AddColumn(
    GroupID, 
    "Values", 
    each if Table.First([tblZ])[Category] = "Temporary" then "Yes" else "No"
  ), 
  ValuesTable = Table.RemoveColumns(AddedValue, {"tblZ"}), 
  PreResult = Table.NestedJoin(Tbl1, {"ID"}, ValuesTable, {"ID"}, "ColValues"), 
  Result = Table.ExpandTableColumn(PreResult, "ColValues", {"Values"})
in
  Result

Solving the challenge of If in a group of with Excel

Excel solution 1 for If in a group of, proposed by Rick Rothstein:

=VSTACK("Value",IF(XLOOKUP(A2:A14&"Z",A2:A14&B2:B14,C2:C14,,0)="Temporary","Yes","No"))

Excel solution 2 for If in a group of, proposed by Rick Rothstein:

=VSTACK("Value",IF(XLOOKUP(A2:A14&"Z",A2:A14&B2:B14,C2:C14)="Temporary","Yes","No"))

Excel solution 3 for If in a group of, proposed by محمد حلمي:

=LET(
    
    a,
    A2:A14,
    
    b,
    B2:B14,
    
    HSTACK(
        a,
        ASC(
            B2:C14
        ),
        
        IF(
            XMATCH(
                a&"Z"&"Temporary",
                a&b&C2:C14
            )=
            XMATCH(
                a&"Z",
                a&b
            ),
            
            "Yes",
            "No"
        )
    )
)

Excel solution 4 for If in a group of, proposed by محمد حلمي:

=LET(
    
    a,
    A2:A14,
    
    b,
    B2:B14,
    
    HSTACK(
        a,
        ASC(
            B2:C14
        ),
        
        IF(
            XMATCH(
                a&B7&C2,
                a&b&C2:C14
            )=
            XMATCH(
                a&B7,
                a&b
            ),
            "Yes",
            "No"
        )
    )
)

Excel solution 5 for If in a group of, proposed by 🇰🇷 Taeyong Shin:

=HSTACK(A2:C14, IF(XLOOKUP(A2:A14&"Z", A2:A14&B2:B14, C2:C14="Temporary"), "Yes", "No"))

Excel solution 6 for If in a group of, proposed by Aditya Kumar Darak 🇮🇳:

= LET(
    
     _i,
    
     A2:A14,
    
     _e,
    
     B2:B14,
    
     _c,
    
     C2:C14,
    
     _calc,
    
     IF(
         XLOOKUP(
             _i & "-Z",
              _i & "-" & _e,
              _c
         ) = "Temporary",
         
          "Yes",
         
          "No"
     ),
    
     _f,
    
     HSTACK(
         _i,
          _e,
          _c,
          _calc
     ),
    
     IF(
         ISBLANK(
             _f
         ),
          "",
          _f
     )
)

Excel solution 7 for If in a group of, proposed by Stefan Olsson:

=LAMBDA(
    _i,
    _a,
    ArrayFormula(
        IF(
            FIND(
                _i&"Z"&"Temporary",
                _a
            )=FIND(
                _i&"Z",
                _a
            ),
            "Yes",
            "No"
        )
    )
)(A2:A14,
    TEXTJOIN(
        "",
        false,
        A2:C14
    ))

Excel variant
=LAMBDA(
    _i,
    _a,
    IF(
        FIND(
            _i&"Z"&"Temporary",
            _a
        )=FIND(
            _i&"Z",
            _a
        ),
        "Yes",
        "No"
    )
)(A2:A14,
    TEXTJOIN(
        "",
        FALSE,
        A2:C14
    ))

Excel solution 8 for If in a group of, proposed by Viswanathan M B:

=IF(xlookup(A2:A10&"Z", A2:A10&B2:B10, C2:C10,"")="Temporary", "Yes", "No")

Solving the challenge of If in a group of with Power Query

Solving the challenge of If in a group of with Excel

Leave a Reply Cancel reply