Classify Items as Used or New

Generate the Result table from Problem table. – The items may appear with or without s at the end. Ex – Monitor & Monitors – If an item is used, “Used” will appear following the item – If an item is new, then either “New” appears or nothing appears following the word. Ex. – 1 Headset / 3 Monitor Used – Here Headset will be treated as New

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

Solving the challenge of Classify Items as Used or New with Power Query

Power Query solution 1 for Classify Items as Used or New, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Srow = Table.ExpandListColumn(
    Table.TransformColumns(Source, {"Data", each Text.Split(_, " / ")}), 
    "Data"
  ), 
  Scol = Table.TransformColumnTypes(
    Table.SplitColumn(Srow, "Data", Splitter.SplitTextByDelimiter(" "), {"N", "Items", "T"}), 
    {"N", Number.Type}
  ), 
  Clean = Table.TransformColumns(
    Scol, 
    {{"Items", each Text.TrimEnd(_, "s")}, {"T", each if _ = null then "New" else _}}
  ), 
  Grouped = Table.Group(
    Clean, 
    {"Items"}, 
    {
      {"T", each Table.Pivot(_, List.Distinct([T]), "T", "N", List.Sum)}, 
      {"Total", each List.Sum([N]), Int64.Type}
    }
  ), 
  Expanded = Table.ExpandTableColumn(Grouped, "T", List.Distinct(Clean[T]))
in
  Expanded

Power Query solution 2 for Classify Items as Used or New, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Srow = Table.ExpandListColumn(
    Table.TransformColumns(Source, {"Data", each Text.Split(_, " / ")}), 
    "Data"
  ), 
  Scol = Table.TransformColumnTypes(
    Table.SplitColumn(
      Srow, 
      "Data", 
      each Splitter.SplitTextByAnyDelimiter({" ", "s "})(_ & " New"), 
      {"N", "Items", "T"}
    ), 
    {"N", Number.Type}
  ), 
  Grouped = 
    let
      Head = List.Distinct(Scol[T])
    in
      Table.ExpandTableColumn(
        Table.Group(
          Scol, 
          {"Items"}, 
          {{"T", each Table.Pivot(_, Head, "T", "N", List.Sum)}, {"Total", each List.Sum([N])}}
        ), 
        "T", 
        Head
      )
in
  Grouped

Power Query solution 3 for Classify Items as Used or New, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  SplitDataBySlash = Table.ExpandListColumn(
    Table.TransformColumns(
      Source, 
      {
        {
          "Data", 
          Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Data"
  ), 
  SplitDataBySpace = Table.SplitColumn(
    SplitDataBySlash, 
    "Data", 
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
    {"Quantity", "Items", "Description"}
  ), 
  RemoveS = Table.TransformColumns(
    SplitDataBySpace, 
    {
      {
        "Items", 
        each if Text.EndsWith(_, "s") then Text.Range(_, 0, Text.Length(_) - 1) else _, 
        type text
      }
    }
  ), 
  ReplacedNull = Table.ReplaceValue(RemoveS, null, "New", Replacer.ReplaceValue, {"Description"}), 
  FormatQuantity = Table.TransformColumnTypes(ReplacedNull, {{"Quantity", Int64.Type}}), 
  GroupedRows = Table.Group(
    FormatQuantity, 
    {"Items", "Description"}, 
    {{"Quantity", each List.Sum([Quantity]), type nullable number}}
  ), 
  PivotedDescription = Table.Pivot(
    GroupedRows, 
    List.Distinct(GroupedRows[Description]), 
    "Description", 
    "Quantity", 
    List.Sum
  ), 
  AddedTotal = Table.AddColumn(PivotedDescription, "Total", each ([New] ?? 0) + ([Used] ?? 0))
in
  AddedTotal

Power Query solution 4 for Classify Items as Used or New, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(){[Name = "TableData"]}[Content], 
  #"Split Column by Delimiter" = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByDelimiter("/", QuoteStyle.None), 
    {"Data.1", "Data.2", "Data.3"}
  ), 
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(
    #"Split Column by Delimiter", 
    {}, 
    "Attribute", 
    "Value"
  ), 
  #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns", {{"Value", Text.Trim, type text}}), 
  #"Split Column by Delimiter1" = Table.SplitColumn(
    #"Trimmed Text", 
    "Value", 
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), 
    {"Value.1", "Value.2", "Value.3"}
  ), 
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Split Column by Delimiter1", 
    {{"Value.1", Int64.Type}}
  ), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Changed Type1", 
    null, 
    "New", 
    Replacer.ReplaceValue, 
    {"Value.3"}
  ), 
  #"Added Custom" = Table.AddColumn(#"Replaced Value", "Item", each Text.Trim([Value.2], "s")), 
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Attribute", "Value.2"}), 
  #"Pivoted Column" = Table.Pivot(
    #"Removed Columns", 
    List.Distinct(#"Removed Columns"[Value.3]), 
    "Value.3", 
    "Value.1", 
    List.Sum
  ), 
  #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum({[New], [Used]}))
in
  #"Added Custom1"

Power Query solution 5 for Classify Items as Used or New, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Added = Table.AddColumn(Source, "Custom", each Text.Split([Data], " / "))[[Custom]], 
  Expand = Table.ExpandListColumn(Added, "Custom"), 
  Split = Table.SplitColumn(
    Expand, 
    "Custom", 
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
    {"Count", "Items", "UoN"}
  ), 
  Type = Table.TransformColumnTypes(Split, {{"Count", Int64.Type}}), 
  TrimS = Table.TransformColumns(Type, {"Items", each Text.TrimEnd(_, "s")}), 
  #"Replaced Value" = Table.ReplaceValue(TrimS, null, "New", Replacer.ReplaceValue, {"UoN"}), 
  Grouped = Table.Group(
    #"Replaced Value", 
    {"Items"}, 
    {
      {
        "All", 
        each Table.PromoteHeaders(
          Table.Transpose(Table.Group(_, {"UoN"}, {{"Count", each List.Sum([Count])}}))
        )
      }, 
      {"Total", each List.Sum([Count])}
    }
  ), 
  Sol = Table.ExpandTableColumn(Grouped, "All", {"New", "Used"})
in
  Sol

Power Query solution 6 for Classify Items as Used or New, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  tab = Table.AddColumn(Fonte, "Personalizar", each Text.Split([Data], " / ")), 
  exp = Table.ExpandListColumn(tab, "Personalizar"), 
  add = Table.AddColumn(
    exp, 
    "Personalizar.1", 
    each [
      Qtd  = Text.Select([Personalizar], {"0" .. "9"}), 
      Tipo = if Text.Contains([Personalizar], "Used") = true then "Used" else "New"
    ]
  ), 
  exp2 = Table.ExpandRecordColumn(add, "Personalizar.1", {"Qtd", "Tipo"}), 
  div = Table.SplitColumn(
    exp2, 
    "Personalizar", 
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
    3
  ), 
  tab2 = Table.AddColumn(div, "Items", each Text.TrimEnd([Personalizar.2], "s"))[
    [Items], 
    [Tipo], 
    [Qtd]
  ], 
  ind = Table.AddIndexColumn(tab2, "Índice", 0, 1, Int64.Type), 
  piv = Table.Pivot(ind, List.Distinct(ind[Tipo]), "Tipo", "Qtd"), 
  rev = Table.RemoveColumns(piv, {"Índice"}), 
  tip = Table.TransformColumnTypes(rev, {{"New", type number}, {"Used", type number}}), 
  gp = Table.Group(
    tip, 
    {"Items"}, 
    {
      {"New", each List.Sum([New]), type nullable number}, 
      {"Used", each List.Sum([Used]), type nullable number}
    }
  ), 
  res = Table.AddColumn(gp, "Total", each List.Sum({[New], [Used]}))
in
  res

Power Query solution 7 for Classify Items as Used or New, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.ExpandListColumn(
    Table.TransformColumns(
      Source, 
      {
        {
          "Data", 
          Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Data"
  ), 
  Trim = Table.TransformColumns(Split, {{"Data", Text.Trim, type text}}), 
  AddCondition = Table.AddColumn(
    Trim, 
    "Condition", 
    each if Text.Contains([Data], "Used") then "Used" else "New"
  ), 
  Split2 = Table.SplitColumn(
    AddCondition, 
    "Data", 
    Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), 
    {"Quantity", "Item"}
  ), 
  ReType = Table.TransformColumnTypes(Split2, {{"Quantity", Int64.Type}, {"Item", type text}}), 
  Split3 = Table.SplitColumn(
    ReType, 
    "Item", 
    Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), 
    {"Item"}
  ), 
  AddItems = Table.RemoveColumns(
    Table.AddColumn(
      Split3, 
      "Items", 
      each 
        if Text.EndsWith([Item], "s") then
          Text.RemoveRange([Item], Text.Length([Item]) - 1)
        else
          [Item]
    ), 
    "Item"
  ), 
  Pivot = Table.Pivot(
    AddItems, 
    List.Distinct(AddItems[Condition]), 
    "Condition", 
    "Quantity", 
    List.Sum
  ), 
  AddTotal = Table.AddColumn(Pivot, "Total", each List.Sum({[New], [Used]}))
in
  AddTotal

Power Query solution 8 for Classify Items as Used or New, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData60"]}[Content], 
  Transform = Table.TransformColumns(
    Source, 
    {
      {
        "Data", 
        each 
          let
            _Records = List.Transform(
              Text.Split(_, "/"), 
              each 
                let
                  _l = Text.Split(Text.Trim(_), " "), 
                  _Item = 
                    if (Text.EndsWith(_l{1}, "s")) then
                      Text.RemoveRange(_l{1}, Text.Length(_l{1}) - 1)
                    else
                      _l{1}, 
                  _Type = if (List.Count(_l) = 3) then _l{2} else "New"
                in
                  [Items = _Item, Type = _Type, Count = Number.From(_l{0})]
            )
          in
            _Records
      }
    }
  ), 
  Expand = Table.FromRecords(
    Table.ExpandListColumn(Transform, "Data")[Data], 
    type table [Items = Text.Type, Type = Text.Type, Count = Number.Type]
  ), 
  Pivot = Table.Pivot(Expand, List.Distinct(Expand[Type]), "Type", "Count", List.Sum), 
  Add_Total = Table.AddColumn(Pivot, "Total", each List.Sum({[New], [Used]}))
in
  Add_Total

Power Query solution 9 for Classify Items as Used or New, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.ExpandListColumn(
    Table.TransformColumns(Source, {{"Data", each List.Transform(Text.Split(_, "/"), Text.Trim)}}), 
    "Data"
  ), 
  Proper = Table.FromRecords(
    Table.TransformRows(
      Split, 
      each 
        let
          words = Text.Split([Data], " ")
        in
          [
            Items = Text.TrimEnd(words{1}, "s"), 
            Count = Number.From(words{0}), 
            Desc  = if List.Count(words) = 2 then "New" else words{2}
          ]
    )
  ), 
  Pivot = Table.Pivot(Proper, List.Distinct(Proper[Desc]), "Desc", "Count", List.Sum), 
  Total = Table.AddColumn(Pivot, "Total", each List.Sum({[New], [Used]})), 
  Sort = Table.Sort(Total, each List.PositionOf(Proper[Items], [Items]))
in
  Sort

Power Query solution 10 for Classify Items as Used or New, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  S1 = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), 
    {"Data.1", "Data.2", "Data.3"}
  ), 
  S2 = Table.FromColumns({List.RemoveNulls(List.Combine(Table.ToColumns(S1)))}), 
  S3 = Table.SplitColumn(
    S2, 
    "Column1", 
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c))
  ), 
  S4 = Table.AddColumn(
    S3, 
    "Custom", 
    each if Text.Contains([Column1.2], "Used") then "Used" else "New"
  ), 
  S6 = Table.TransformColumnTypes(S4, {{"Column1.1", Int64.Type}}), 
  R1 = Table.ReplaceValue(S6, "Used", "", Replacer.ReplaceText, {"Column1.2"}), 
  R2 = Table.ReplaceValue(R1, " New", "", Replacer.ReplaceText, {"Column1.2"}), 
  R3 = Table.ReplaceValue(R2, "Headsets", "Headset", Replacer.ReplaceText, {"Column1.2"}), 
  R4 = Table.ReplaceValue(R3, "Monitors", "Monitor", Replacer.ReplaceText, {"Column1.2"}), 
  R5 = Table.ReplaceValue(R4, "Docks", "Dock", Replacer.ReplaceText, {"Column1.2"}), 
  S7 = Table.TransformColumns(R5, {{"Column1.2", Text.Trim, type text}}), 
  S8 = Table.Pivot(S7, List.Distinct(S7[Custom]), "Custom", "Column1.1", List.Sum), 
  S10 = Table.AddColumn(S8, "Total", each [New] + [Used], Int64.Type)
in
  S10

Power Query solution 11 for Classify Items as Used or New, proposed by Anup Kumar:

letely menu driven (except steps renaming) to Load the data in Pivot Friendly format :

let
 Source = Excel.CurrentWorkbook(){[Name="data_Tbl"]}[Content],
 SplitC = Table.SplitColumn(Source, "Data", Splitter.SplitTextByDelimiter("/"), {"Data.1", "Data.2", "Data.3"}),
 Unpvt = Table.UnpivotOtherColumns(SplitC, {}, "Attribute", "Value"),
 RemovC = Table.RemoveColumns(Unpvt,{"Attribute"}),
 Trim = Table.TransformColumns(RemovC,{{"Value", Text.Trim, type text}}),
 SpltC1 = Table.SplitColumn(Trim, "Value", Splitter.SplitTextByDelimiter(" "), {"Value.1", "Value.2", "Value.3"}),
 RplV1 = Table.ReplaceValue(SpltC1,null,"New",Replacer.ReplaceValue,{"Value.3"}),
 AddC = Table.AddColumn(RplV1, "Space", each " "),
 MrgC = Table.CombineColumns(AddC,{"Value.2", "Space"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
 RplV2 = Table.ReplaceValue(MrgC,"s ","",Replacer.ReplaceText,{"Merged"}),
 Trim2 = Table.TransformColumns(RplV2,{{"Merged", Text.Trim, type text}}),
 RodrC = Table.ReorderColumns(Trim2,{"Merged", "Value.3", "Value.1"}),
 RnmC = Table.RenameColumns(RodrC,{{"Merged", "Item"}, {"Value.3", "Condition"}, {"Value.1", "Count"}}),
 Load = Table.TransformColumnTypes(RnmC,{{"Count", Int64.Type}})
in
 Load

Solving the challenge of Classify Items as Used or New with Excel

Excel solution 1 for Classify Items as Used or New, proposed by Bo Rydobon 🇹🇭:

=LET(z,TEXTSPLIT(CONCAT(SUBSTITUTE(A2:A8," / ","_")&"_")," ","_",1,,"New"),y,LEFT(z,LEN(z)-(RIGHT(z)="s")),b,INDEX(y,,2),c,DROP(y,,2),d,UNIQUE(b),e,TOROW(UNIQUE(c)),
HSTACK(VSTACK("Items",d),VSTACK(HSTACK(e,"Total"),MAP(d&HSTACK(e,"*"),LAMBDA(i,SUM(TAKE(y,,1)*ISNUMBER(SEARCH(i,b&c))))))))

Excel solution 2 for Classify Items as Used or New, proposed by 🇰🇷 Taeyong Shin:

=LET(RE,REGEXEXTRACT,t,REGEXREPLACE(TEXTSPLIT(TEXTJOIN(" / ",,A2:A8),," / "),"(?





Excel solution 3 for Classify Items as Used or New, proposed by Oscar Mendez Roca Farell:

=LET(_m; IFNA(DROP(REDUCE("";DROP(TOCOL(
 REDUCE("";A2:A8;LAMBDA(i; x; VSTACK(i;
 TEXTSPLIT(x;" / "))));3);1);
 LAMBDA(i; x; VSTACK( i; TEXTSPLIT(x;" "))));1);"New"); 
_d; MAP(INDEX(_m;;2);
 LAMBDA(i; IFNA(TEXTBEFORE(i&" ";"s ");i)));
_u; UNIQUE(_d);_e; UNIQUE(INDEX(_m;;3));
_v; MAP(INDEX(_m;;1);
 LAMBDA(i; N(--i)));
_r; REDUCE(TRANSPOSE(_e);SEQUENCE(ROWS(_u));
 LAMBDA(i; x; VSTACK(i;
 BYCOL(N(_d & INDEX(_m;;3)=TRANSPOSE(INDEX(_u; x)&_e))*_v;
 LAMBDA(i; SUM(i))))));
_t; MAP(BYROW(_r; LAMBDA(s; SUM(s)));
 LAMBDA(i; SI(i=0;"Total";i)));_p; VSTACK("Items";_u);HSTACK(_p;_r;_t))

Inspired in Victor Momoh (MVP, MOS)




Excel solution 4 for Classify Items as Used or New, proposed by Duy Tùng:

=LET(I,INDEX,a,TEXT&SPLIT(ARRAYTOTEXT(TEXTSPLIT(TEXTJOIN(" / ",,A2:A8),," / "))," ",", ",,,"New"),b,I(a,,2),c,IF(RIGHT(b)="s",LEFT(b,LEN(b)-1),b),d,
DROP(PIVOTBY(HSTACK(XMATCH(c,c),c),I(a,,3),I(a,,1)*1,SUM,,0),,1),IF(I(d,1)&I(d,,1)="","Items",d))




Excel solution 5 for Classify Items as Used or New, proposed by Guillermo Arroyo:

=LET(d,IFNA(TEXTSPLIT(TEXTJOIN("/",,SUBSTITUTE(A2:A8," / ","/"))," ","/"),"New"),g,LAMBDA(j,k,CHOOSECOLS(j,k)),m,HSTACK(d,IF(RIGHT(g(d,2))="s",MID(g(d,2),1,LEN(g(d,2))-1),g(d,2))),p,UNIQUE(g(m,4)),q,TRANSPOSE(UNIQUE(g(m,3))),r,MMULT(--(p=TRANSPOSE(g(m,4))),--(g(m,3)=q)*g(m,1)),VSTACK(HSTACK("Items",q,"Total"),HSTACK(p,IF(r,r,""),MMULT(r,{1;1}))))




Excel solution 6 for Classify Items as Used or New, proposed by Mohamed Helmy:

=LET(
r,TEXTSPLIT( CONCAT(A2:A8&"/ "),,"/ ",1),
e,TEXTSPLIT(r," ")+0,
x,TEXTAFTER(r," "),
c,TEXTSPLIT(x,," "),
h,IF(RIGHT(c)="s",LEFT(c,LEN(c)-1),c),
u,UNIQUE(h),
to,MAP(u,LAMBDA(a,SUM(TOCOL(IF(FIND(a,x),e),2)))),
used,MAP(u,LAMBDA(a,IFERROR(SUM(TOCOL(IF(IFERROR(FIND(a&" Used",x),FIND(a&"s Used",x)),e),2)),))),
rr,VSTACK(HSTACK("Items","New","Used","Total"),
HSTACK(u,to-used,used,to)),IF(rr=0,"",rr))




Excel solution 7 for Classify Items as Used or New, proposed by Mohamed Helmy:

=LET(
r,TEXTSPLIT(CONCAT(A2:A8&"/ "),,"/ ",1),
e,TEXTSPLIT(r," ")+0,
x,TEXTAFTER(r," "),
c,TEXTSPLIT(x,," "),
u,UNIQUE(IF(RIGHT(c)="s",LEFT(c,LEN(c)-1),c)),
o,MAP(u,LAMBDA(a,SUM(TOCOL(IF(FIND(a,x),e),2)))),
s,MAP(u,LAMBDA(a,IFERROR(SUM(TOCOL(
IF(IFERROR(FIND(a&" Used",x),FIND(a&"s Used",x)),e),2)),))),
v,VSTACK({"Items","New","Used","Total"},HSTACK(u,o-s,s,o)),
IF(v=0,"",v))



&&

Solving the challenge of Classify Items as Used or New with Power Query

Solving the challenge of Classify Items as Used or New with Excel

Leave a Reply Cancel reply