Mark Completed Tasks Only

Transpose the table as shown. If a task is completed, its Completion Date is non blank.

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

Solving the challenge of Mark Completed Tasks Only with Power Query

Power Query solution 1 for Mark Completed Tasks Only, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  F = (t, f) => Text.Combine(Table.SelectRows(t, each [Completion Date] = null = f)[Tasks], ", "), 
  S = Table.Group(
    Source, 
    {"Project"}, 
    {{"Completed Tasks", each F(_, false)}, {"Not Completed Tasks", each F(_, true)}}
  )
in
  S

Power Query solution 2 for Mark Completed Tasks Only, proposed by Kris Jaganah:

let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.TransformColumns(
    A, 
    {"Completion Date", each (if _ = null then "Not " else "") & "Completed Tasks"}
  ), 
  C = Table.Pivot(
    B, 
    List.Distinct(B[#"Completion Date"]), 
    "Completion Date", 
    "Tasks", 
    each Text.Combine(_, ", ")
  )
in
  C

Power Query solution 3 for Mark Completed Tasks Only, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  TaskType = Table.TransformColumns(
    Source, 
    {"Completion Date", each (if _ = null then "Not " else "") & "Completed"}
  ), 
  Return = Table.Pivot(
    TaskType, 
    List.Distinct(TaskType[Completion Date]), 
    "Completion Date", 
    "Tasks", 
    (x) => Text.Combine(x, ", ")
  )
in
  Return

Power Query solution 4 for Mark Completed Tasks Only, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.Group(
    Source, 
    {"Project"}, 
    {
      {
        "Complete Task", 
        each Text.Combine(Table.SelectRows(_, each [Completion Date] <> null)[Tasks], ", ")
      }, 
      {
        "Not Complete Task", 
        each Text.Combine(Table.SelectRows(_, each [Completion Date] = null)[Tasks], ", ")
      }
    }
  )
in
  Sol

Power Query solution 5 for Mark Completed Tasks Only, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  res = Table.Group(
    Fonte, 
    {"Project"}, 
    {
      {
        "Completed Tasks", 
        each Text.Combine(Table.SelectRows(_, each [Completion Date] <> null)[Tasks], ", ")
      }, 
      {
        "Not Completed Tasks", 
        each Text.Combine(Table.SelectRows(_, each [Completion Date] = null)[Tasks], ", ")
      }
    }
  )
in
  res

Power Query solution 6 for Mark Completed Tasks Only, proposed by Hussein SATOUR:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped Rows" = Table.Group(
    Source, 
    {"Project"}, 
    {
      {
        "Completed Tasks", 
        each Text.Combine(Table.SelectRows(_, each ([Completion Date] <> null))[Tasks], ", ")
      }, 
      {
        "Not Completed Tasks", 
        each Text.Combine(Table.SelectRows(_, each ([Completion Date] = null))[Tasks], ", ")
      }
    }
  )
in
  #"Grouped Rows"

Power Query solution 7 for Mark Completed Tasks Only, proposed by Abdallah Ally:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Transform = List.Transform(
    List.Distinct(Source[Project]), 
    each [
      a = Table.SelectRows(Source, (x) => x[Project] = _ and x[Completion Date] <> null)[Tasks], 
      b = Table.SelectRows(Source, (x) => x[Project] = _ and x[Completion Date] = null)[Tasks], 
      c = {_, Text.Combine(a, ", "), Text.Combine(b, ", ")}
    ][c]
  ), 
  Result = Table.FromRows(Transform, {"Project", "Completed Tasks", "Not Completed Tasks"})
in
  Result

Power Query solution 8 for Mark Completed Tasks Only, proposed by Abdallah Ally:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddCol1 = Table.AddColumn(Source, "C", each if [Completion Date] <> null then [Tasks] else null), 
  AddCol2 = Table.AddColumn(AddCol1, "N", each if [Completion Date] = null then [Tasks] else null), 
  Result = Table.Group(
    AddCol2, 
    "Project", 
    {{"Completed", each Text.Combine([C], ", ")}, {"Not Completed", each Text.Combine([N], ", ")}}
  )
in
  Result

Power Query solution 9 for Mark Completed Tasks Only, proposed by Ramiro Ayala Chávez:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.ExpandRecordColumn(
    Table.AddColumn(
      S, 
      "A", 
      each [
        C = if [Completion Date] <> null then [Tasks] else null, 
        N = if [Completion Date] = null then [Tasks] else null
      ]
    ), 
    "A", 
    {"C", "N"}
  ), 
  Sol = Table.Group(
    a, 
    {"Project"}, 
    {
      {"Completed Tasks", each Text.Combine([C], ", ")}, 
      {"Not Completed Tasks", each Text.Combine([N], ", ")}
    }
  )
in
  Sol

Power Query solution 10 for Mark Completed Tasks Only, proposed by Albert Cid Cañigueral:

let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a = Table.TransformColumns(
    Origen, 
    {"Completion Date", each if _ = null then "Not Completed" else "Completed"}
  ), 
  b = Table.Pivot(
    a, 
    List.Distinct(a[Completion Date]), 
    "Completion Date", 
    "Tasks", 
    each Text.Combine(_, ", ")
  )
in
  b

Power Query solution 11 for Mark Completed Tasks Only, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.TransformColumns(
    Source, 
    {{"Completion Date", each if _ = null then "Not Completed" else "Completed"}}
  ), 
  B = Table.Group(
    A, 
    {"Project", "Completion Date"}, 
    {{"Tasks", each Text.Combine([Tasks], ", "), type text}}
  ), 
  C = Table.Pivot(B, List.Distinct(B[#"Completion Date"]), "Completion Date", "Tasks")
in
  C

Power Query solution 12 for Mark Completed Tasks Only, proposed by Meganathan Elumalai:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.Group(
    Source, 
    {"Project"}, 
    {
      {
        "Completed Tasks", 
        each Text.Combine(
          List.Distinct(Table.SelectRows(_, (f) => f[Completion Date] <> null)[Tasks]), 
          ", "
        )
      }, 
      {
        "Not Completed Tasks", 
        each Text.Combine(
          List.Distinct(Table.SelectRows(_, (f) => f[Completion Date] = null)[Tasks]), 
          ", "
        )
      }
    }
  )
in
  Result

Power Query solution 13 for Mark Completed Tasks Only, proposed by Ahmed Ariem:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
from = Table.TransformColumns(Source,{"Completion Date",(x)=> if x is datetime then "Completed Tasks" else "Not Completed Tasks"}),
Pivot = Table.Pivot(from, List.Distinct(from[#"Completion Date"]), "Completion Date", "Tasks",Combiner.CombineTextByDelimiter(", "))
in
Pivot
------
attached file
https://1drv.ms/x/s!AiUZ0Ws7G26RkGyb__9KoCR00zy4?e=rXJDMV

Power Query solution 14 for Mark Completed Tasks Only, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Flag = Table.TransformColumns(
    Source, 
    {"Completion Date", each if _ = null then "Not Completed Task" else "Completed Task 
 "}
  ), 
  Result = Table.Pivot(
    Flag, 
    List.Distinct(Flag[#"Completion Date"]), 
    "Completion Date", 
    "Tasks", 
    each Text.Combine(_, ",")
  )
in
  Result

Power Query solution 15 for Mark Completed Tasks Only, proposed by Francesco Bianchi 🇮🇹:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  GroupedRows = Table.Group(
    Source, 
    {"Project"}, 
    {
      {
        "Completed Tasks", 
        each Text.Combine(Table.SelectRows(_, each [Completion Date] <> null)[Tasks], ", "), 
        type text
      }, 
      {
        "Not Completed Tasks", 
        each Text.Combine(Table.SelectRows(_, each [Completion Date] = null)[Tasks], ", "), 
        type text
      }
    }
  )
in
  GroupedRows

Power Query solution 16 for Mark Completed Tasks Only, proposed by Gertjan Davies:

let
  Source = Problem, 
  Status = Table.AddColumn(
    Source, 
    "Status", 
    each if [Completion Date] = null then "Uncompleted" else "Completed"
  ), 
  Group = Table.Group(Status, {"Project", "Status"}, {{"Tasks", each _[Tasks], type list}}), 
  Extract = Table.TransformColumns(
    Group, 
    {"Tasks", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
  ), 
  Pivot = Table.Pivot(Extract, List.Distinct(Extract[Status]), "Status", "Tasks")
in
  Pivot

Power Query solution 17 for Mark Completed Tasks Only, proposed by Amit Rathi:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  GroupedTable = Table.Group(
    Source, 
    {"Project"}, 
    {
      {
        "Completed Tasks", 
        each Text.Combine(
          List.Transform(
            Table.SelectRows(_, each ([Completion Date] <> null and [Completion Date] <> ""))[Tasks], 
            Text.From
          ), 
          ", "
        )
      }, 
      {
        "Not Completed Tasks", 
        each Text.Combine(
          List.Transform(
            Table.SelectRows(_, each ([Completion Date] = null or [Completion Date] = ""))[Tasks], 
            Text.From
          ), 
          ", "
        )
      }
    }
  )
in
  GroupedTable

Solving the challenge of Mark Completed Tasks Only with Excel

Excel solution 1 for Mark Completed Tasks Only, proposed by Bo Rydobon 🇹🇭:

=PIVOTBY(
    A2:A17,
    REPT(
        "Not ",
        C2:C17=0
    ) & "Completed "&B1,
    B2:B17,
    ARRAYTOTEXT,
    ,
    0,
    ,
    0
)

=LET(p,
    A2:A17,
    u,
    UNIQUE(
        p
    ),
    L,
    LAMBDA(y,
    MAP(u,
    LAMBDA(v,
    ARRAYTOTEXT(FILTER(B2:B17,
    (p=v)*(y-(C2:C17>0)),
    ""))))),
    HSTACK(
        u,
        L(
            0
        ),
        L(
            1
        )
    ))

Excel solution 2 for Mark Completed Tasks Only, proposed by Julian Poeltl:

=LET(P,
    A2:A17,
    T,
    B2:B17,
    C,
    C2:C17,
    REDUCE(HSTACK(
        "Project",
        "Completed Tasks",
        "Not Completed Tasks"
    ),
    UNIQUE(
        P
    ),
    LAMBDA(A,
    B,
    VSTACK(A,
    HSTACK(B,
    IFERROR(TEXTJOIN(", ",
    ,
    FILTER(T,
    (P=B)*(C))),
    ""),
    IFERROR(TEXTJOIN(", ",
    ,
    FILTER(T,
    (P=B)*(C=0))),
    ""))))))

Excel solution 3 for Mark Completed Tasks Only, proposed by Aditya Kumar Darak 🇮🇳:

=PIVOTBY(A2:A17, IF(C2:C17 = "", "Not ", "") & "Completed", B2:B17, ARRAYTOTEXT, 0, 0, , 0)

Excel solution 4 for Mark Completed Tasks Only, proposed by Oscar Mendez Roca Farell:

=LET(
    c,
     C2:C17,
     F,
     LAMBDA(
         i,
          BYROW(
              WRAPROWS(
                  REPT(
                      B2:B17,
                       i
                  ),
                   4
              ),
               LAMBDA(
                   r,
                    TEXTJOIN(
                        ", ",
                         ,
                         r
                    )
               )
          )
     ),
     t,
     "Completed ",
     VSTACK(
         HSTACK(
             A1,
              {"",
              "Not"}&t&B1
         ),
          HSTACK(
              UNIQUE(
                  A2:A17
              ),
               F(
                   c>0
               ),
               F(
                   c=0
               )
          )
     )
)

Excel solution 5 for Mark Completed Tasks Only, proposed by Duy Tùng:

=LET(
    a,
    PIVOTBY(
        A2:A17,
        IF(
            C2:C17>0,
            "",
            "Not "
        )&"Completed Tasks",
        B2:B17,
        ARRAYTOTEXT,
        ,
        0,
        ,
        0
    ),
    IF(
        TAKE(
            a,
            1
        )&TAKE(
            a,
            ,
            1
        )="",
        A1,
        a
    )
)

Excel solution 6 for Mark Completed Tasks Only, proposed by Sunny Baggu:

=LET(
 p,
     A2:A17,
    
 t,
     B2:B17,
    
 cd,
     C2:C17,
    
 u,
     UNIQUE(
         p
     ),
    
 MAKEARRAY(
 ROWS(
     u
 ),
    
 3,
    
 LAMBDA(r,
     c,
    
 LET(
 _a,
     INDEX(
         u,
          r,
          1
     ),
    
 _b,
     p = _a,
    
 INDEX(
 HSTACK(
 _a,
    
 ARRAYTOTEXT(FILTER(t,
     _b * (cd <> ""),
     "")),
    
 ARRAYTOTEXT(FILTER(t,
     _b * (cd = ""),
     ""))
 ),
    
 c
 )
 )
 )
 )
)

Excel solution 7 for Mark Completed Tasks Only, proposed by LEONARD OCHEA 🇷🇴:

=LET(
    a,
    A2:A17,
    b,
    B2:B17,
    c,
    C2:C17,
    d,
    "Completed "&B1,
    PIVOTBY(
        a,
        IF(
            c,
            d,
            "Not "&d
        ),
        b,
        ARRAYTOTEXT,
        ,
        0,
        ,
        0
    )
)

Excel solution 8 for Mark Completed Tasks Only, proposed by Pieter de B.:

=REDUCE(HSTACK(
    A1,
    {"",
    "Not "}&"Completed Tasks"
),
    UNIQUE(
        A2:A17
    ),
    LAMBDA(a,
    u,
    LET(b,
    LAMBDA(c,
    ARRAYTOTEXT(FILTER(B2:B17,
    (A2:A17=u)*c,
    ""))),
    VSTACK(
        a,
        HSTACK(
            u,
            b(
                C2:C17
            ),
            b(
                NOT(
                C2:C17
            )
            )
        )
    ))))

_x000D_

Excel solution 9 for Mark Completed Tasks Only, proposed by Hamidi Hamid:

=LET(bb,
    B2:B17,
    aa,
    A2:A17,
    x,
    UNIQUE(
        aa
    ),
    y,
    IF(
        C2:C17<>"",
        bb,
        ""
    ),
    z,
    IF(
        C2:C17<>"",
        bb,
        ""
    ),
    w,
    MAP(x,
    LAMBDA(a,
    TEXTJOIN(", ",
    ,
    ,
    FILTER(y,
    (aa=a))))),
    q,
    MAP(x,
    LAMBDA(a,
    TEXTJOIN(", ",
    ,
    FILTER(z,
    (aa=a))))),
    t,
    IF(
        z="",
        bb,
        ""
    ),
    r,
    MAP(
        x,
        LAMBDA(
            a,
            TEXTJOIN(
                ", ",
                ,
                FILTER(
                    t,
                    aa=a
                )
            )
        )
    ),
    HSTACK(
        x,
        q,
        r
    ))

_x000D_ _x000D_

Excel solution 10 for Mark Completed Tasks Only, proposed by Asheesh Pahwa:

=LET(
    p,
    A2:A17,
    t,
    B2:B17,
    d,
    C2:C17,
    
    I,
    IF(
        d,
        "C",
        "N"
    ),
    u,
    UNIQUE(
        p
    ),
    DROP(
        REDUCE(
            "",
            u,
            LAMBDA(
                x,
                y,
                VSTACK(
                    x,
                    
                    LET(
                        f,
                        FILTER(
                            HSTACK(
                                B2:B17,
                                I
                            ),
                            p=y
                        ),
                        c,
                        ARRAYTOTEXT(
                            FILTER(
                                TAKE(
                                    f,
                                    ,
                                    1
                                ),
                                TAKE(
                                    f,
                                    ,
                                    -1
                                )="C",
                                ""
                            )
                        ),
                        nc,
                        HSTACK(
                            y,
                            c,
                            ARRAYTOTEXT(
                                FILTER(
                                    TAKE(
                                    f,
                                    ,
                                    1
                                ),
                                    TAKE(
                                    f,
                                    ,
                                    -1
                                )="N"
                                )
                            )
                        ),
                        nc
                    )
                )
            )
        ),
        1
    )
)

_x000D_ _x000D_

Excel solution 11 for Mark Completed Tasks Only, proposed by ferhat CK:

=DROP(
    REDUCE(
        0,
        UNIQUE(
            A2:A17
        ),
        LAMBDA(
            x,
            y,
            VSTACK(
                x,
                LET(
                    a,
                    FILTER(
                        A2:C17,
                        A2:A17=y
                    ),
                    d,
                    CHOOSECOLS(
                        a,
                        2
                    ),
                    dd,
                    CHOOSECOLS(
                        a,
                        3
                    ),
                    HSTACK(
                        y,
                        TEXTJOIN(
                            ",",
                            TRUE,
                            IF(
                                dd<>"",
                                d,
                                ""
                            )
                        ),
                        TEXTJOIN(
                            ",",
                            TRUE,
                            IF(
                                dd="",
                                d,
                                ""
                            )
                        )
                    )
                )
            )
        )
    ),
    1
)

_x000D_ _x000D_

Excel solution 12 for Mark Completed Tasks Only, proposed by Albert Cid Cañigueral:

=PIVOTBY(A2:A17,IF(ISNUMBER(C2:C17),"Completed","Not Completed"),B2:B17,ARRAYTOTEXT,,0,,0)

_x000D_ _x000D_

Excel solution 13 for Mark Completed Tasks Only, proposed by Andy Heybruch:

=LET(_proj,
    A2:A17,
    _p,
    UNIQUE(
        _proj
    ),
    _tasks,
    B2:B17,
    _compl,
    ISBLANK(
        C2:C17
    ),
    
_complete,
    VSTACK("Completed Tasks",
    SCAN("",
    _p,
    LAMBDA(A,
    V,
    ARRAYTOTEXT(FILTER(_tasks,
    (_proj=V)*(_compl=FALSE),
    ""))))),
    
_notcomplete,
    VSTACK("Not Completed Tasks",
    SCAN("",
    _p,
    LAMBDA(A,
    V,
    ARRAYTOTEXT(FILTER(_tasks,
    (_proj=V)*(_compl=TRUE),
    ""))))),
    
HSTACK(
    VSTACK(
        "Project",
        _p
    ),
    _complete,
    _notcomplete
))

_x000D_ _x000D_

Excel solution 14 for Mark Completed Tasks Only, proposed by Meganathan Elumalai:

=LET(Tbl,
    A2:C17,
    _C1,
    TAKE(
        Tbl,
        ,
        1
    ),
    P,
    UNIQUE(
        TAKE(
        Tbl,
        ,
        1
    )
    ),
    _C2,
    INDEX(
        Tbl,
        ,
        2
    ),
    _C3,
    INDEX(
        Tbl,
        ,
        3
    ),
    REDUCE({"Project",
    "Completed Tasks",
    "Not Completed Tasks"},
    P,
    LAMBDA(a,
    v,
    VSTACK(a,
    HSTACK(v,
    ARRAYTOTEXT(UNIQUE(FILTER(_C2,
    (_C3<>"")*(_C1=v),
    ""))),
    ARRAYTOTEXT(UNIQUE(FILTER(_C2,
    (_C3="")*(_C1=v),
    ""))))))))

_x000D_ _x000D_

Excel solution 15 for Mark Completed Tasks Only, proposed by Imam Hambali:

=PIVOTBY(A2:A17,IF(C2:C17>0,"Completed Tasks","Not Completed Tasks"),B2:B17,ARRAYTOTEXT,0,0,,0)

_x000D_ _x000D_

Excel solution 16 for Mark Completed Tasks Only, proposed by Milan Shrimali:

=X),
    IFERROR(
        HSTACK(
            JOIN(
                ",",
                FILTER(
                    FILTER(
                        A,
                        CHOOSECOLS(
                            A,
                            2
                        )<>""
                    ),
                    {1,
                    0}
                )
            ),
            JOIN(
                ",",
                FILTER(
                    FILTER(
                        A,
                        CHOOSECOLS(
                            A,
                            2
                        )=""
                    ),
                    {1,
                    0}
                )
            )
        ),
        ""
    )))))

_x000D_ _x000D_

Excel solution 17 for Mark Completed Tasks Only, proposed by Nicolas Micot:

=LET(_projects;
    UNIQUE(
        A2:A17
    );
    
_completed;
    MAP(_projects;
    LAMBDA(l_project;
    JOINDRE.TEXTE(", ";
    VRAI;
    FILTRE(B2:B17;
    (A2:A17=l_project)*(C2:C17<>"");
    ""))));
    
_notCompleted;
    MAP(_projects;
    LAMBDA(l_project;
    JOINDRE.TEXTE(", ";
    VRAI;
    FILTRE(B2:B17;
    (A2:A17=l_project)*(C2:C17="");
    ""))));
    
ASSEMB.H(
    _projects;
    _completed;
    _notCompleted
))

_x000D_ _x000D_

Excel solution 18 for Mark Completed Tasks Only, proposed by El Badlis Mohd Marzudin:

=LET(
    
    a,
    PIVOTBY(
        A2:A17,
        IF(
            C2:C17,
            "",
            "Not"
        )&
        "Completed",
        B2:B17,
        ARRAYTOTEXT,
        0,
        0,
        ,
        0
    ),
    
    b,
    SEQUENCE(
        ROWS(
            a
        ),
        COLUMNS(
            a
        )
    ),
    
    IF(
        b=1,
        "Project",
        a
    )
)

_x000D_ _x000D_

Excel solution 19 for Mark Completed Tasks Only, proposed by RIJESH T.:

=LET(pro,
    UNIQUE(
        A2:A17
    ),
    
Ct,
    MAP(pro,
    LAMBDA(a,
    TEXTJOIN(", ",
    ,
    FILTER(B2:B17,
    (A2:A17=a)*($C$2:$C$17<>""),
    "")))),
    
NCt,
    MAP(pro,
    LAMBDA(a,
    TEXTJOIN(", ",
    ,
    FILTER(B2:B17,
    (A2:A17=a)*($C$2:$C$17=""),
    "")))),
    
Hdg,
    HSTACK(
        "Project",
        "Completed Tasks",
        "Not Completed Tasks"
    ),
    
Ans,
    HSTACK(
        pro,
        Ct,
        NCt
    ),
    
VSTACK(
    Hdg,
    Ans
))

_x000D_ _x000D_

Excel solution 20 for Mark Completed Tasks Only, proposed by Md Ismail Hosen:

=DROP(
    PIVOTBY(
        A2:A17,
        IF(
            ISNUMBER(
                C2:C17
            ),
            "Completed",
            "Not Completed"
        ),
        B2:B17,
        LAMBDA(
            grp,
            TEXTJOIN(
                ", ",
                TRUE,
                grp
            )
        ),
        0,
        0,
        ,
        0
    ),
    1
)

_x000D_ _x000D_

Excel solution 21 for Mark Completed Tasks Only, proposed by Songglod P.:

=LET(p,
    UNIQUE(
        A2:A17
    ),
    ib,
    ISBLANK(
        C2:C17
    ),
    func,
    LAMBDA(x,
    y,
    ARRAYTOTEXT(FILTER(B2:B17,
    (A2:A17=x)*y,
    ""))),
    HSTACK(
        p,
        MAP(
            p,
            LAMBDA(
                r,
                func(
                    r,
                    NOT(
                        ib
                    )
                )
            )
        ),
        MAP(
            p,
            LAMBDA(
                r,
                func(
                    r,
                    ib
                )
            )
        )
    ))

_x000D_ _x000D_

Excel solution 22 for Mark Completed Tasks Only, proposed by Stefan Alexandrov:

=VSTACK(
    {"Project",
    "Completed",
    "Not completed"},
    
    HSTACK(
        UNIQUE(
            A2:A17
        ),
        
        MAP(
            UNIQUE(
                $A$2:$A$17
            ),
            LAMBDA(
                x,
                TEXTJOIN(
                    ",",
                    1,
                    FILTER(
                        IF(
                            A2:A17=x,
                            $B$2:$B$17,
                            ""
                        ),
                        C2:C17<>0
                    )
                )
            )
        ),
        
        MAP(
            UNIQUE(
                $A$2:$A$17
            ),
            LAMBDA(
                x,
                TEXTJOIN(
                    ",",
                    1,
                    FILTER(
                        IF(
                            A2:A17=x,
                            $B$2:$B$17,
                            ""
                        ),
                        C2:C17=0
                    )
                )
            )
        )
        
    )
)

_x000D_

Solving the challenge of Mark Completed Tasks Only with Python

_x000D_

Python solution 1 for Mark Completed Tasks Only, proposed by Konrad Gryczan, PhD:

import pandas as pd
path = "PQ_Challenge_218.xlsx"
input = pd.read_excel(path, usecols="A:C")
test = pd.read_excel(path, usecols="E:G", nrows=4).rename(columns=lambda x: x.replace(".1", ""))
input["Status"] = input["Completion Date"].apply(lambda x: "Not Completed Tasks" if pd.isnull(x) else "Completed Tasks")
input = input.drop(columns="Completion Date").pivot_table(index="Project", columns="Status", aggfunc=lambda x: ', '.join(map(str, x)))
input.columns = input.columns.droplevel()
input = input.reset_index().rename_axis(None, axis=1)
print(input.equals(test)) # True

_x000D_

Solving the challenge of Mark Completed Tasks Only with Python in Excel

_x000D_

Python in Excel solution 1 for Mark Completed Tasks Only, proposed by Alejandro Campos:

data_df = xl("A1:C17", headers=True)
 .assign(Status=lambda df: df["Completion Date"]
 .apply(lambda x: "Completed" if pd.notnull(x) else "Not Completed"))
 .drop(columns='Completion Date')
result = data_df.groupby('Project')['Tasks'].agg(
 Completed=lambda x: ', '.join(x[data_df['Status'] == 'Completed']),
 Not_Completed=lambda x: ', '.join(x[data_df['Status'] == 'Not Completed']))
 .reset_index()
result

_x000D_ _x000D_

Python in Excel solution 2 for Mark Completed Tasks Only, proposed by Abdallah Ally:

df = xl("A1:C17", headers=True)
# Perform data manipulation
df['C'] = df.apply(lambda x: x[1] if pd.notna(x[2]) else '', axis=1)
df['N'] = df.apply(lambda x: x[1] if pd.isna(x[2]) else '', axis=1)
df = df.groupby('Project').agg(
 C = ('C', lambda x: ', '.join(filter(None, x))), 
 N = ('N', lambda x: ', '.join(filter(None, x)))
).reset_index()
df = df.rename(columns={'C': 'Completed Tasks', 'N': 'Not Completed Tasks'})
df

_x000D_

Solving the challenge of Mark Completed Tasks Only with R

_x000D_

R solution 1 for Mark Completed Tasks Only, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_218.xlsx"
input = read_excel(path, range = "A1:C17")
test = read_excel(path, range = "E1:G5") %>%
 replace_na(list(`Completed Tasks` = "", `Not Completed Tasks` = ""))
result = input %>%
 mutate(has_date = ifelse(is.na(`Completion Date`), "Not Completed Tasks", "Completed Tasks")) %>%
 select(-`Completion Date`) %>%
 pivot_wider(names_from = has_date, values_from = Tasks, values_fn = list) %>%
 mutate(`Completed Tasks` = map_chr(`Completed Tasks`, ~paste(.x, collapse = ", ")),
 `Not Completed Tasks` = map_chr(`Not Completed Tasks`, ~paste(.x, collapse = ", "))) 
identical(result, test)
#> [1] TRUE

_x000D_

Solving the challenge of Mark Completed Tasks Only with Power Query

Solving the challenge of Mark Completed Tasks Only with Excel

Solving the challenge of Mark Completed Tasks Only with Python

Solving the challenge of Mark Completed Tasks Only with Python in Excel

Solving the challenge of Mark Completed Tasks Only with R

Leave a Reply Cancel reply