Find Date Range Alphabetically

Similar kind of problem appeared in MS Techcommunity and this is a variation of that. Generate the result table from the problem table as shown.

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

Solving the challenge of Find Date Range Alphabetically with Power Query

Power Query solution 1 for Find Date Range Alphabetically, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  TextDate = Table.TransformColumns(
    Source, 
    {{"Column1", each Text.BeforeDelimiter(Text.From(_, "en-US"), " ")}}
  ), 
  Grouped = Table.Combine(
    Table.Group(
      TextDate, 
      "Seq", 
      {
        "A", 
        each 
          let
            a = _, 
            c = Table.ToColumns(Table.Skip(a, 2)), 
            r = Table.ToRows(a), 
            n = Table.RowCount(a) - 1, 
            cn = Table.ColumnNames(a), 
            b = Table.FromColumns(
              {
                List.Repeat({r{0}{0}}, n), 
                List.Repeat({r{0}{2}}, n), 
                List.Repeat({r{0}{1}}, n), 
                c{1}, 
                c{2}, 
                c{3} & {"Sub Total"}, 
                c{4} & {List.Sum(c{4})}
              }, 
              {cn{0}, cn{2}, cn{1}} & {"No"} & List.Skip(r{1}, 2)
            )
          in
            b
      }, 
      0, 
      (b, e) => Number.From(Value.Is(e, type number))
    )[A]
  ), 
  GrandTotal = 
    let
      a = Table.ToColumns(Grouped), 
      b = List.RemoveLastN(a, 2) & {a{5} & {"Grand Total"}} & {a{6} & {List.Sum(a{6}) / 2}}
    in
      Table.FromColumns(b, Table.ColumnNames(Grouped))
in
  GrandTotal

Power Query solution 2 for Find Date Range Alphabetically, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  AddRecords = Table.AddColumn(Source, "Records", each if [Seq] = null then null else _), 
  Expanded = Table.ExpandRecordColumn(
    AddRecords, 
    "Records", 
    {"City", "State"}, 
    {"City.1", "State.1"}
  ), 
  FilledDown = Table.FillDown(Expanded, {"Seq", "City.1", "State.1"}), 
  Filtered = Table.SelectRows(FilledDown, each [City] is number), 
  Renamed = Table.RenameColumns(
    Filtered, 
    {
      {"City", "No"}, 
      {"State", "Class"}, 
      {"Column1", "Date"}, 
      {"Sub Total", "Amount"}, 
      {"State.1", "State"}, 
      {"City.1", "City"}
    }
  ), 
  SortedColumn = Table.SelectColumns(
    Renamed, 
    {"Seq", "State", "City", "No", "Class", "Date", "Amount"}
  ), 
  Grouped = Table.Group(
    SortedColumn, 
    {"Seq", "State", "City"}, 
    {{"Amount", each List.Sum([Amount]), type number}}
  ), 
  AddedSubTotal = Table.AddColumn(Grouped, "Date", each "Sub-Total"), 
  Appended = SortedColumn & AddedSubTotal, 
  SortedRows = Table.Sort(Appended, {{"Seq", Order.Ascending}, {"Date", Order.Ascending}}), 
  Final = SortedRows
    & Table.FromRecords({[Date = "Grand-Total", Amount = List.Sum(SortedRows[Amount])]})
in
  Final

Power Query solution 3 for Find Date Range Alphabetically, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  AddRecords = Table.AddColumn(Source, "Records", each if [Seq] = null then null else _), 
  Expanded = Table.ExpandRecordColumn(
    AddRecords, 
    "Records", 
    {"City", "State"}, 
    {"City.1", "State.1"}
  ), 
  FilledDown = Table.FillDown(Expanded, {"Seq", "City.1", "State.1"}), 
  Filtered = Table.SelectRows(FilledDown, each [City] is number), 
  Renamed = Table.RenameColumns(
    Filtered, 
    {
      {"City", "No"}, 
      {"State", "Class"}, 
      {"Column1", "Date"}, 
      {"Sub Total", "Amount"}, 
      {"State.1", "State"}, 
      {"City.1", "City"}
    }
  ), 
  SortedColumn = Table.SelectColumns(
    Renamed, 
    {"Seq", "State", "City", "No", "Class", "Date", "Amount"}
  ), 
  Grouped = Table.Group(
    SortedColumn, 
    {"Seq", "State", "City"}, 
    {{"Amount", each List.Sum([Amount]), type number}}
  ), 
  AddedSubTotal = Table.AddColumn(Grouped, "Date", each "Sub-Total"), 
  Appended = SortedColumn & AddedSubTotal, 
  SortedRows = Table.Sort(Appended, {{"Seq", Order.Ascending}, {"Date", Order.Ascending}})
in
  SortedRows

Power Query solution 4 for Find Date Range Alphabetically, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 City = Table.AddColumn(Source, "Custom", each if Value.Type ([Seq]) = type number then [City] else null),
 State = Table.AddColumn(City, "Custom2", each if Value.Type ([Seq]) = type number then [State] else null),
 Rellenar = Table.FillDown(State,{"Seq", "Custom", "Custom2"}),
 Agrupar = Table.Group(Rellenar, {"Seq"}, {{"Count", each
 let
 a = _,
 b = Record.TransformFields(Table.First (a), {{ "City", each Replacer.ReplaceValue(_, _, null)}, { "State", each Replacer.ReplaceValue(_, _, null)}, { "Column1", each Replacer.ReplaceValue(_, _, "Sub Total")}}),
 c = Table.Skip (Table.InsertRows(a, Table.RowCount(_), {b}),2)
 in c
 }})[[Count]],

Power Query solution 5 for Find Date Range Alphabetically, proposed by Luan Rodrigues:

let
  Fonte = Data, 
  pb = Table.FillDown(Fonte, {"Seq"}), 
  gp = Table.Group(
    pb, 
    {"Seq"}, 
    {{"Contagem", each Table.SelectRows(Table.AddIndexColumn(_, "Rank", 0, 1), each [Rank] <> 0)}}
  ), 
  exp = Table.ExpandTableColumn(
    gp, 
    "Contagem", 
    {"City", "State", "Column1", "Sub Total", "Rank"}, 
    {"No", "Class", "Date", "Amount", "Rank"}
  ), 
  p1 = Table.SelectRows(exp, each ([Rank] <> 0) and ([Date] <> "Date")), 
  tb2 = Table.Group(
    pb, 
    {"Seq"}, 
    {{"Contagem", each Table.SelectRows(Table.AddIndexColumn(_, "Rank", 0, 1), each [Rank] = 0)}}
  ), 
  exp2 = Table.ExpandTableColumn(
    tb2, 
    "Contagem", 
    {"City", "State", "Column1", "Sub Total", "Rank"}, 
    {"City", "State", "No", "Amount", "Class"}
  ), 
  ad = Table.AddColumn(exp2, "Date", each "Sub Total"), 
  ac = Table.Combine({p1, ad}), 
  cl = Table.Sort(
    ac, 
    {{"Seq", Order.Ascending}, {"Class", Order.Descending}, {"No", Order.Ascending}}
  ), 
  tab_f = Table.ReplaceValue(cl, 0, null, Replacer.ReplaceValue, {"Class"}), 
  col_r = Table.SelectColumns(tab_f, {"Seq", "State", "City", "No", "Class", "Date", "Amount"}), 
  Result = Table.Combine(
    {
      Table.FillUp(col_r, {"State", "City"}), 
      Table.FromRecords({[Date = "Grand Total", Amount = List.Sum(p1[Amount])]})
    }
  )
in
  Result

Power Query solution 6 for Find Date Range Alphabetically, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ColNames = {"Seq", "State", "City", "No", "Class", "Date", "Amount"}, 
  FnTransform = (Tbl as table) =>
    let
      FillDown = Table.FillDown(Tbl, {"Seq"}), 
      C_1 = List.FirstN(Record.ToList(FillDown{0}), 3), 
      C_2 = List.Transform(Table.ToRows(Table.Skip(FillDown, 2)), each C_1 & List.Skip(_))
        & {C_1 & {null, null, "Sub Total"} & {Tbl[Sub Total]{0}}}, 
      C_3 = Table.FromRows(C_2, ColNames), 
      Transformed = Table.ReorderColumns(
        Table.ReplaceValue(
          C_3, 
          "Sub Total", 
          null, 
          (a, b, c) => if a = "Sub Total" then b else Date.From(a), 
          {"Date"}
        ), 
        {"Seq", "State", "City", "No", "Class", "Date", "Amount"}
      )
    in
      Transformed, 
  Grouped = Table.Group(
    Source, 
    {"Seq"}, 
    {{"All", each FnTransform(_)}}, 
    GroupKind.Local, 
    (x, y) => Number.From(y[Seq] <> null)
  )[All], 
  ExpectedOutput = Table.Combine(
    Grouped
      & {
        Table.FromRecords(
          {
            [
              Date   = "Grand Total", 
              Amount = List.Sum(List.Transform(Grouped, each List.Last(_[Amount])))
            ]
          }, 
          ColNames, 
          MissingField.UseNull
        )
      }
  )
in
  ExpectedOutput

Power Query solution 7 for Find Date Range Alphabetically, proposed by Matthias Friedmann:

let
 Source = Excel.CurrentWorkbook(){[Name="CityState"]}[Content],
 Renamed = Table.RenameColumns(Source,{{"Column1", "Date"}, {"Sub Total", "Amount"}}),
 Filtered = Table.SelectRows(Renamed, each ([Date] <> "Date")),
 Duplicated = Table.DuplicateColumn(Filtered, "City", "No"),
 Duplicated1 = Table.DuplicateColumn(Duplicated, "State", "Class"),
 Replaced = Table.ReplaceValue(Duplicated1,"",each [Date],(a,b,c)=>if c = null then a else null,{"City", "State"}),
 Replaced1 = Table.ReplaceValue(Replaced,"",each [Date],(a,b,c)=>if c = null then null else a,{"No", "Class"}),
 Replaced2 = Table.ReplaceValue(Replaced1,null,"Sub Total",Replacer.ReplaceValue,{"Date"}),
 Filled = Table.FillDown(Replaced2,{"Seq", "City", "State"}),
 Reordered = Table.ReorderColumns(Filled,{"Seq", "State", "City", "No", "Class", "Date", "Amount"}),
 Sorted = Table.Sort(Reordered,{{"Seq", Order.Ascending}, {"Date", Order.Ascending}}),
 Subs = Table.SelectRows(Sorted, each ([Date] = "Sub Total")),
 GrandTotal = Sorted & hashtag#table({"Date", "Amount"}, {{"Grand Total", List.Sum(Subs[Amount])}})
in
 GrandTotal

Power Query solution 8 for Find Date Range Alphabetically, proposed by Victor Wang:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 FillDown = Table.FillDown(Source,{"Seq"}),
 Group = Table.Group(FillDown, {"Seq"}, {{"all", each Table.FromColumns({{[Seq]{0}},{[State]{0}}, {[City]{0}}, {Table.FromRows(Table.ToRows(Table.RemoveColumns(Table.Skip(_,2), "Seq")) & {{null, null, "Sub Total", [Sub Total]{0}}})}})}}),
 Combine = Table.Combine(Group[all]),
 Expand = Table.ExpandTableColumn(Combine, "Column4", {"Column1", "Column2", "Column3", "Column4"}, {"No","Class", "Date", "Amount"}),
 Rename = Table.RenameColumns(Expand,{{"Column1", "Seq"}, {"Column2", "State"}, {"Column3", "City"}}),
 GrandTotal = Rename & hashtag#table({"Date", "Amount"}, {{"Grand Total", List.Sum(Table.SelectRows(Rename, each [Date] = "Sub Total")[Amount])}}),
 DateToText = Table.TransformColumns(GrandTotal,{{"Date", each if _ is datetime then Text.From(Date.From(_)) else _, type text}})
in
 DateToText

Power Query solution 9 for Find Date Range Alphabetically, proposed by Krzysztof Kominiak:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Tab1 = Table.SelectColumns(Table.SelectRows(Source, each ([Seq] <> null)), List.FirstN(Table.ColumnNames(Source),3)),
 Tab2 = Table.Group(Table.SelectRows(Table.FillDown(Source,{"Seq"}), each ([Column1] <> null and [Column1] <> "Date") ), {"Seq"}, {{"NT", each _ & hashtag#table(Table.ColumnNames(_),{{ List.First(_[Seq]) ,"","","SubTotal", List.Sum(_[Sub Total]) }}) , type table}}),
 Expand = Table.ExpandTableColumn( Table.ExpandTableColumn( Table.NestedJoin(Tab1,{"Seq"},Tab2,{"Seq"},"Tab2",JoinKind.LeftOuter) , "Tab2", {"NT"}, {"NT"}) , "NT", {"City", "State", "Column1", "Sub Total"}, {"No", "Class", "Date", "Amount"}),
 ResDate = Table.TransformColumns(Expand, {"Date", each try Text.From(Date.From(_)) otherwise "SubTotal" } ),
 Grand = hashtag#table(Table.ColumnNames(ResDate), {{"","","","","","GrandTotal", List.Sum(ResDate[Amount])}}),
 Result = ResDate&Grand
in
 Result

Solving the challenge of Find Date Range Alphabetically with Python

Python solution 1 for Find Date Range Alphabetically, proposed by Igor Perković:

As usual data cleansing/transforming day... 😀 
https://gist.github.com/igorp74/9360f434ece68a6ebb75ad8bb62dd8b3

Solving the challenge of Find Date Range Alphabetically with Excel VBA

Excel VBA solution 1 for Find Date Range Alphabetically, proposed by Bo Rydobon 🇹🇭:

=LET(z,A2:E21,s,SCAN(,TAKE(z,,1),LAMBDA(c,n,IF(n,n,c))),VSTACK(REDUCE(HSTACK(A1,C1,B1,"No",INDEX(z,2,{3,4,5})),UNIQUE(s),
LAMBDA(c,n,LET(y,FILTER(z,s=n),x,SORTBY(TAKE(y,1,3),{1,3,2}),VSTACK(c,IFNA(HSTACK(x,VSTACK(DROP(y,2,1),HSTACK("","","Sub total",TAKE(y,1,-1)))),x))))),
HSTACK("","","","","","Grand Total",SUMIFS(TAKE(z,,-1),TAKE(z,,1),">0"))))

Excel VBA solution 2 for Find Date Range Alphabetically, proposed by Zoran Milokanović:

WITH
DATA_PREPARATION
AS
(
 SELECT
 TO_NUMBER(MAX(DECODE(F.SEQ, '', NULL, F.SEQ)) OVER (ORDER BY F.ORDINAL_NUMBER ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS SEQ
 , F.CITY
 ,F.STATE
 ,F.COLUMN1
 ,F.SUB_TOTAL
 FROM
 (
 SELECT
 ROW_NUMBER() OVER () AS ORDINAL_NUMBER
 ,D.*
 FROM DATA D
 ) F
)
SELECT
 ST.SEQ
,ST.STATE
, ST.CITY
,TO_NUMBER(I.CITY) AS NO
,I.STATE AS CLASS
,I.COLUMN1 AS DATE
,TO_NUMBER(I.SUB_TOTAL) AS AMOUNT
FROM DATA_PREPARATION ST
JOIN DATA_PREPARATION I ON ST.SEQ = I.SEQ
WHERE
 ST.COLUMN1 = ''
AND I.COLUMN1 NOT IN ('', 'Date')
UNION ALL
SELECT
 ST.SEQ
,ST.STATE
, ST.CITY
,NULL AS NO
,NULL AS CLASS
,'Sub Total' AS DATE
,TO_NUMBER(ST.SUB_TOTAL) AS AMOUNT
FROM DATA_PREPARATION ST
WHERE
 ST.COLUMN1 = ''
UNION ALL
SELECT
 NULL AS SEQ
,NULL AS STATE
,NULL AS CITY
,NULL AS NO
,NULL AS CLASS
,'Grand Total' AS DATE
,SUM(TO_NUMBER(ST.SUB_TOTAL)) AS AMOUNT 
FROM DATA_PREPARATION ST
WHERE
 ST.COLUMN1 = ''
ORDER BY
 1, 4
;

Excel VBA solution 3 for Find Date Range Alphabetically, proposed by محمد حلمي:

=LET(v,SCAN(0,A2:A21,LAMBDA(a,v,IF(v,v,a))),x,"",VSTACK(
REDUCE(HSTACK(A1,C1,B1,"No",C3:E3),UNIQUE(v),
LAMBDA(q,w,VSTACK(q,LET(i,FILTER(B2:E21,v=w),
IFNA(HSTACK(w,IF(VSTACK(TOCOL(--TAKE(i,,1),2),1),
INDEX(i,1,{2,1})),DROP(i,2)),
HSTACK(w,x,x,x,x,"Sub Total",MAX(DROP(i,,3)))))))),
HSTACK(x,x,x,x,x,"Grand Total",SUM(E2:E21)/2)))

Excel VBA solution 4 for Find Date Range Alphabetically, proposed by محمد حلمي:

=LET(r,"",
w,SCAN(0,A2:A21,LAMBDA(a,d,IF(d,d,a))),
x,REDUCE(HSTACK(A1,C1,B1,"No",C3,D3,E3),UNIQUE(w),
LAMBDA(a,d,LET(
i,FILTER(B2:E21,w=d),
u,INDEX(TAKE(i,1,2),{2,1}),
VSTACK(a,IFNA(HSTACK(d,IFNA(HSTACK(u,
VSTACK(DROP(i,2),
HSTACK(r,r,"Sub Total",TAKE(i,1,-1)))),u)),d))))),
VSTACK(x,
HSTACK(r,r,r,r,r,"Grand Total",SUM(TAKE(x,,-1))/2)))

Excel VBA solution 5 for Find Date Range Alphabetically, proposed by محمد حلمي:

=LET(
u,FILTER(A2:E21,B2:B21<>"Seq"),
v,SCAN(A2,TAKE(u,,1),LAMBDA(a,d,IF(d="",a,d))),
VSTACK(HSTACK(v,DROP(REDUCE(0,v,
LAMBDA(d,a,VSTACK(d,
INDEX(FILTER(B2:C21,a=A2:A21),{2,1})))),1),
DROP(REDUCE(0,SEQUENCE(MAX(A2:A21)),
LAMBDA(a,d,LET(b,FILTER(u,v=d),VSTACK(a,
VSTACK(DROP(b,1,1),
HSTACK("","","Sub Total",TAKE(b,1,-1))))))),1)),
HSTACK("","","","","","Grand Total",SUM(E2:E21)/2)))

Excel VBA solution 6 for Find Date Range Alphabetically, proposed by Brian Julius:

https://gist.github.com/bjulius/8f33ec296cc5effe9ddd21bdb5544a8a

&&&

Solving the challenge of Find Date Range Alphabetically with Power Query

Solving the challenge of Find Date Range Alphabetically with Python

Solving the challenge of Find Date Range Alphabetically with Excel VBA

Leave a Reply Cancel reply