Home » Create Date Range Based on Rules

Create Date Range Based on Rules

Generate the result table as shown. You need to generate dates as per the interval.

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

Solving the challenge of Create Date Range Based on Rules with Power Query

Power Query solution 1 for Create Date Range Based on Rules, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rec = Table.ExpandRecordColumn(
    Table.AddColumn(
      Source, 
      "R", 
      each 
        let
          Seq = {1 .. [Period]}
        in
          Record.FromList(
            List.Transform(
              Seq, 
              (n) =>
                Date.ToText(
                  Date.AddDays(
                    Date.AddMonths(
                      Date.AddDays(Date.From([Start Date]), 1), 
                      n
                        * Number.RoundDown(
                          3
                            * Number.Power(
                              2, 
                              Text.PositionOf("MQHY", Text.Start([Interval], 1)) - 1
                            )
                        )
                    ), 
                    - 1
                  ), 
                  "yyyy-MM-dd"
                )
            ), 
            List.Transform(Seq, each "Date" & Text.From(_))
          )
    ), 
    "R", 
    List.Transform({1 .. List.Max(Source[Period])}, each "Date" & Text.From(_))
  )
in
  Rec
Power Query solution 2 for Create Date Range Based on Rules, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rec = Table.ExpandRecordColumn(
    Table.AddColumn(
      Source, 
      "R", 
      each 
        let
          Seq = {1 .. [Period]}
        in
          Record.FromList(
            List.Transform(
              Seq, 
              (n) =>
                Date.ToText(
                  Date.AddMonths(
                    Date.From([Start Date]), 
                    n
                      * Number.RoundDown(
                        3 * Number.Power(2, Text.PositionOf("MQHY", Text.Start([Interval], 1)) - 1)
                      )
                  ), 
                  "yyyy-MM-dd"
                )
            ), 
            List.Transform(Seq, each "Date" & Text.From(_))
          )
    ), 
    "R", 
    List.Transform({1 .. List.Max(Source[Period])}, each "Date" & Text.From(_))
  )
in
  Rec
Power Query solution 3 for Create Date Range Based on Rules, proposed by Aditya Kumar Darak 🇮🇳: 
https://gist.github.com/Hermione-Granger-1176/d87c0fab75a6c0f634293f37af032009
Power Query solution 4 for Create Date Range Based on Rules, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Meses = ([Year = 12, Month = 1, Half Yearly = 6, Quarter = 3]), 
  Col = List.Accumulate(
    {1 .. List.Max(Source[Period])}, 
    Source, 
    (s, c) =>
      Table.AddColumn(
        s, 
        "Date" & Text.From(c), 
        each 
          if c > [Period] then
            null
          else
            Date.ToText(
              Date.AddMonths(
                Date.From([Start Date]), 
                Record.ToList(Meses){List.PositionOf(Record.FieldNames(Meses), [Interval])} * c
              ), 
              "yyyy-MM-dd"
            )
      )
  )
in
  Col
Power Query solution 5 for Create Date Range Based on Rules, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  Col = Table.DuplicateColumn(Fonte, "Interval", "Interval_c"), 
  Sub = {{"Year", 12}, {"Month", 1}, {"Half Yearly", 6}, {"Quarter", 3}}, 
  sub1 = Table.TransformColumns(
    Col, 
    {
      {
        "Interval_c", 
        each Text.Combine(
          List.Transform(List.ReplaceMatchingItems(Lines.FromText(_), Sub), Text.From), 
          " "
        ), 
        type text
      }
    }
  ), 
  seq = Table.AddColumn(sub1, "Personalizar", each {0 .. [Period]}), 
  exp = Table.ExpandListColumn(seq, "Personalizar"), 
  rec = Table.AddColumn(
    exp, 
    "Personalizar1", 
    each [
      a = Date.From([Start Date]), 
      b = Date.EndOfMonth(Date.From([Start Date])), 
      c = Date.AddMonths(a, + Number.From([Interval_c]) * [Personalizar]), 
      d = Date.EndOfMonth(Date.AddMonths(a, + Number.From([Interval_c]) * [Personalizar])), 
      e = if a = b then d else c
    ][e]
  ), 
  Tipo = Table.TransformColumnTypes(rec, {{"Personalizar1", type date}}, "en-US"), 
  gp = Table.Group(
    Tipo, 
    {"Period", "Interval"}, 
    {{"Date", each Text.Combine(List.Transform([Personalizar1], Text.From), "|")}}
  ), 
  cnt = List.Max(
    Table.AddColumn(
      gp, 
      "Personalizar", 
      each List.Count(Text.ToList(Text.Select([Date], {"|"}))) + 1
    )[Personalizar]
  ), 
  Result = Table.SplitColumn(gp, "Date", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), cnt)
in
  Result
Power Query solution 6 for Create Date Range Based on Rules, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Replacements = List.Buffer({{"Year", 12}, {"Month", 1}, {"Half Yearly", 6}, {"Quarter", 3}}), 
  AddedCustom = Table.AddColumn(
    Source, 
    "Dates", 
    each List.Transform(
      List.Skip(
        List.Numbers(0, [Period] + 1, List.ReplaceMatchingItems({[Interval]}, Replacements){0})
      ), 
      (m) => Date.ToText(Date.AddMonths(Date.From([Start Date]), m), "yyyy-MM-dd")
    )
  ), 
  Custom = List.Transform(
    {1 .. List.Max(List.Transform(AddedCustom[Dates], List.Count))}, 
    each "Date" & Text.From(_)
  ), 
  RecordfromList = Table.ReplaceValue(
    AddedCustom, 
    null, 
    null, 
    (a, b, c) => Record.FromList(a, List.FirstN(Custom, List.Count(a))), 
    {"Dates"}
  ), 
  ExpectedOutput = Table.ExpandRecordColumn(RecordfromList, "Dates", Custom, Custom)
in
  ExpectedOutput
Power Query solution 7 for Create Date Range Based on Rules, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
Part2:
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"In-Month"}),
 Pivot = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[List]), "List", "Date"),
 Split = Table.SplitColumn(Pivot, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}),
 Ch2 = Table.TransformColumnTypes(Split,{{"Merged.1", Int64.Type}, {"Merged.2", type text}, {"Merged.3", type date}}),
 Rename = Table.RenameColumns(Ch2,{{"Merged.1", "Period"}, {"Merged.2", "Interval"}, {"Merged.3", "Start Date"}}),
 Ch3 = Table.TransformColumnTypes(Rename,{{"Period", Int64.Type}, {"Interval", type text}, {"Start Date", type date}, {"Date 1", type date}, {"Date 2", type date}, {"Date 3", type date}, {"Date 4", type date}, {"Date 5", type date}, {"Date 6", type date}, {"Date 7", type date}, {"Date 8", type date}, {"Date 9", type date}})
in
 Ch3
Power Query solution 8 for Create Date Range Based on Rules, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Ch = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"Interval", type text}, {"Start Date", type date}}),
 ACC = Table.AddColumn(Ch, "In-Month", each if [Interval] = "Year" then 12 else if [Interval] = "Half Yearly" then 6 else if [Interval] = "Quarter" then 3 else 1),
 AC = Table.AddColumn(ACC, "List", each {0.. [Period]}),
 ExL = Table.ExpandListColumn(AC, "List"),
 Ch1 = Table.TransformColumnTypes(ExL,{{"Period", Int64.Type}, {"Interval", type text}, {"Start Date", type date}, {"In-Month", Int64.Type}, {"List", Int64.Type}}),
 Ac1 = Table.AddColumn(Ch1, "Date", each Date.AddMonths([Start Date],[#"In-Month"]*[List])),
 Filter = Table.SelectRows(Ac1, each ([List] <> 0)),
 #"Added Prefix" = Table.TransformColumns(Filter, {{"List", each "Date " & Text.From(_, "en-US"), type text}}),
 #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Prefix", {{"Period", type text}, {"Start Date", type text}}, "en-US"),{"Period", "Interval", "Start Date"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
Power Query solution 9 for Create Date Range Based on Rules, proposed by Matthias Friedmann: 
let
  Source = Excel.CurrentWorkbook(){[Name = "AddPeriod"]}[Content], 
  Type = Table.TransformColumnTypes(Source, {{"Start Date", type date}}), 
  Added = Table.AddColumn(
    Type, 
    "Dates", 
    each [
      period = [Period], 
      interval = 
        if [Interval] = "Year" then
          12
        else if [Interval] = "Half Yearly" then
          6
        else if [Interval] = "Quarter" then
          3
        else
          1, 
      start = [Start Date], 
      list = List.Generate(
        () => [date = Date.AddMonths(start, interval), i = 1], 
        each [i] <= period, 
        each [date = Date.AddMonths(start, interval * ([i] + 1)), i = [i] + 1], 
        each [date]
      ), 
      EoM = 
        if start = Date.EndOfMonth(start) then
          List.Transform(list, each Date.EndOfMonth(_))
        else
          list
    ][EoM]
  ), 
  Extracted = Table.TransformColumns(
    Added, 
    {"Dates", each Text.Combine(List.Transform(_, Text.From), ","), type text}
  ), 
  Split = Table.SplitColumn(
    Extracted, 
    "Dates", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    List.Max(Extracted[Period])
  )
in
  Split
Power Query solution 11 for Create Date Range Based on Rules, proposed by Thomas DUCROQUETZ: 
let
  Source = YourRawData, 
  ModifType = Table.TransformColumnTypes(
    Source, 
    {{"Period", Int64.Type}, {"Interval", type text}, {"Start Date", type date}}
  ), 
  MaxInterval = List.Max(ModifType[Period]), 
  AddDates = List.Accumulate(
    {1 .. MaxInterval}, 
    ModifType, 
    (Table, currentNumber) =>
      Table.AddColumn(
        Table, 
        "Date" & Number.ToText(currentNumber), 
        each 
          let
            dateFunc = 
              if [Interval] = "Year" then
                (d) => Date.AddYears(d, currentNumber)
              else if [Interval] = "Month" then
                (d) => Date.AddMonths(d, currentNumber)
              else if [Interval] = "Half Yearly" then
                (d) => Date.AddMonths(d, 6 * currentNumber)
              else if [Interval] = "Quarter" then
                (d) => Date.AddQuarters(d, currentNumber)
              else
                (d) => d
          in
            if currentNumber <= [Period] then dateFunc([Start Date]) else null, 
        type date
      )
  )
in
  AddDates
Power Query solution 12 for Create Date Range Based on Rules, proposed by Fábio Gatti: 
let
 Source = Table,

 Col_Periods = Table.AddColumn(Source , "Periods", each
 let
 vInterval = [Interval],
 vDate = Date.From([Start Date])
 in
 List.Transform(
 {1..[Period]},
 each
 let 
 vDateValue =
 if vInterval = "Year"
 then Date.AddYears(vDate, _)
 else if vInterval = "Month"
 then Date.AddMonths(vDate, _)
 else if vInterval = "Half Yearly"
 then Date.EndOfMonth(Date.AddMonths(vDate, _ * 6))
 else if vInterval = "Quarter"
 then Date.AddQuarters(vDate, _)
 else
 hashtag#date(1900,1,1) 
 in
 [
 Date = "Date " & Text.From(_),
 DateValue = Date.ToText(vDateValue, "yyyy-MM-dd")
 ]
 )
 ),

 Expand_Periods = Table.ExpandListColumn(Col_Periods, "Periods"),
 Expand_Periods2 = Table.ExpandRecordColumn(Expand_Periods, "Periods", {"Date", "DateValue"}, {"Date", "DateValue"}),
 Pivot_Periods = Table.Pivot(Expand_Periods2, List.Distinct(Expand_Periods2[Date]), "Date", "DateValue")
in
 Pivot_Periods

Solving the challenge of Create Date Range Based on Rules with Excel

Excel solution 1 for Create Date Range Based on Rules, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:C7,p,TAKE(z,,1),s,SEQUENCE(,MAX(p)),
VSTACK(HSTACK(A1:C1,"Date"&s),HSTACK(z,IF(p
Excel solution 2 for Create Date Range Based on Rules, proposed by Rick Rothstein: 
=LET(
    s,
    SEQUENCE(
        ,
        A2
    ),
    l,
    LEFT(
        B2
    ),
    e,
    EDATE(
        C2,
        CHOOSE(
            FIND(
                l,
                "YMHQ"
            ),
            12,
            1,
            6,
            3
        )*s
    ),
    IF(
        EOMONTH(
            C2,
            0
        )=C2,
        EOMONTH(
            e,
            0
        ),
        e
    )
)
Excel solution 3 for Create Date Range Based on Rules, proposed by Zoran Milokanović: 
= 'NO' THEN DATEADD(YEAR, 1, C.START_DATE) ELSE EOMONTH(DATEADD(YEAR, 1, C.START_DATE)) END)
 WHEN 'Half Yearly' THEN (CASE WHEN C.EOM_FLAG = 'NO' THEN DATEADD(QUARTER, 2, C.START_DATE) ELSE EOMONTH(DATEADD(QUARTER, 2, C.START_DATE)) END)
 WHEN 'Quarter' THEN (CASE WHEN C.EOM_FLAG = 'NO' THEN DATEADD(QUARTER, 1, C.START_DATE) ELSE EOMONTH(DATEADD(QUARTER, 1, C.START_DATE)) END)
 WHEN 'Month' THEN (CASE WHEN C.EOM_FLAG = 'NO' THEN DATEADD(MONTH, 1, C.START_DATE) ELSE EOMONTH(DATEADD(MONTH, 1, C.START_DATE)) END)
 END AS DATE) AS START_DATE
 ,C.DATE_SEQ + 1 AS DATE_SEQ
 FROM CALC C
 WHERE
 C.DATE_SEQ <= C.PERIOD
)
SELECT
 P.PERIOD
,P.INTERVAL
,P.[1] AS START_DATE, P.[2] AS DATE1, P.[3] AS DATE2, P.[4] AS DATE3, P.[5] AS DATE4, P.[6] AS DATE5, P.[7] AS DATE6, P.[8] AS DATE7, P.[9] AS DATE8, P.[10] AS DATE9
FROM CALC C
PIVOT
(
 MAX(C.START_DATE)
 FOR C.DATE_SEQ IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
)
Excel solution 4 for Create Date Range Based on Rules, proposed by محمد حلمي: 
=HSTACK(A2:C7,
IFNA(DROP(
REDUCE(0,SEQUENCE(ROWS(B2:B7)),
LAMBDA(a,d,
VSTACK(a,
LET(s,SEQUENCE(,INDEX(A2:A7,d)),
b,INDEX(B2:B7,d),
EDATE(INDEX(C2:C7,d),
IFS(
b="Year",s*12,
b="Half Yearly",s*6,
b="Month",s*1,
b="Quarter",s*3)))))),1),""))
Excel solution 5 for Create Date Range Based on Rules, proposed by محمد حلمي: 
=LET(
s,SEQUENCE(,A2),
EDATE(C2,
IFS(
B2="Year",s*12,
B2="Half Yearly",s*6,
B2="Month",s*1,
B2="Quarter",s*3)))
Excel solution 6 for Create Date Range Based on Rules, proposed by 🇰🇷 Taeyong Shin: 
=LET(
 intv, SWITCH(B2:B7, "Year", 12, "Month", 1, "Half Yearly", 6, "Quarter", 3),
 P, A1:A7,
 Dt, C2:C7,
 nums, MAKEARRAY(ROWS(P), MAX(P), LAMBDA(r,c,
 IF(r = 1, "Date" & c, IF(c > @INDEX(P, r), "", c))
 )),
 HSTACK(A1:C7, VSTACK(TAKE(nums, 1), IFERROR(EDATE(+Dt, intv * DROP(nums, 1)), "")))
)
=LET(
 Intv, VLOOKUP(B2:B7, {"Year",12;"Month",1;"Half Yearly",6;"Quarter",3}, 2, 0),
 n, IFNA(SEQUENCE(, MAX(A2:A7)), A2:A7),
 HSTACK(
 A1:C7,
 VSTACK(
 "Date" & TAKE(n, 1),
 REPT(TEXT(EDATE(+C2:C7, Intv * n), "yyyy-mm-dd"), n <= A2:A7)
 )
 )
)
Excel solution 7 for Create Date Range Based on Rules, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _d, A1:C7,
 _h, TAKE(_d, 1),
 _b, DROP(_d, 1),
 _ct, "MQHY",
 _pd, TAKE(_b, , 1),
 _int, INDEX(_b, 0, 2),
 _dt, --TAKE(_b, , -1),&
 _du, CHOOSE(FIND(LEFT(_int), _ct), 1, 3, 6, 12),
 _mx, SEQUENCE(1, MAX(_pd)),
 _e, LAMBDA(a, b,
 HSTACK(
 a,
 IFS(
 b > _pd,
 "",
 _dt = EOMONTH(_dt, 0),
 EOMONTH(_dt, b * _du),
 1,
 EDATE(_dt, b * _du)
 )
 )
 ),
 _c, REDUCE(_dt, _mx, _e),
 _fh, HSTACK(_h, "Date" & _mx),
 _fb, HSTACK(TAKE(_b, , 2), TEXT(_c, "yyyy-mm-dd")),
 _r, VSTACK(_fh, _fb),
 _r
)
Excel solution 8 for Create Date Range Based on Rules, proposed by Quadri Olayinka Atharu: 
=EDATE(Startdate,SEQUENCE(,Period,Interval,Interval))

Solving the challenge of Create Date Range Based on Rules with SQL

SQL solution 1 for Create Date Range Based on Rules, proposed by Zoran Milokanović: 
1/2
WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
 SELECT
 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ORDERING
 ,D.PERIOD
 ,D.INTERVAL
 ,CAST(CONVERT(VARCHAR, D.START_DATE, 120) AS DATE) AS START_DATE
 FROM DATA D
),

&&

Leave a Reply