locked
Transform Date Range to Dates RRS feed

  • Question

  • I have a table of data, the key columns of which are as per the following:

    [Start.Date] [End.Date] [Day.1] [Day.2] [Day.3] [Day.4] [Day.5] [Day.6] [Day.7]


    [Start.Date] and [End.Date] are dates, defining the first and last dates in a date range. [Day.1], .., [Day.7] are either null, or integer value, defining a status of days of the week in that date range (null= 'not active', <>null='active').

    For each row, I need to convert these 9 columns into a single column; a list of the dates between each Start.Date and End.Date that are 'active'.

    Any help much appreciated! Thanks.

    Friday, March 17, 2017 12:28 PM

Answers

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
        UnpivotedColumns = Table.UnpivotOtherColumns(ChangedType, {"Start Date", "End Date"}, "Attribute", "Value"),
        TransformedValues = Table.TransformColumns(UnpivotedColumns, {"Value", each if _ <> 7 then _ else 0}),
        AddedAllDates = Table.AddColumn(TransformedValues, "All Dates", each List.Dates([Start Date], Number.From([End Date]) - Number.From([Start Date]) + 1, #duration(1, 0, 0, 0))),
        AddedActiveDates = Table.AddColumn(AddedAllDates, "Active Dates", each List.Select([All Dates], (current) => Date.DayOfWeek(current) = [Value])),
        ExpandedActiveDates = Table.ExpandListColumn(AddedActiveDates, "Active Dates"),
        RemovedOtherColumns = Table.SelectColumns(ExpandedActiveDates,{"Active Dates"}),
        SortedDates = Table.Sort(RemovedOtherColumns,{{"Active Dates", Order.Ascending}})
    in
        SortedDates

    • Marked as answer by jl8103 Friday, March 17, 2017 4:24 PM
    Friday, March 17, 2017 4:17 PM

All replies

  • Please check if the following meets your requirement (you need to replace the Source step with your own):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        DayList = {{"Day.1", 0}, {"Day.2", 1}, {"Day.3", 2}, {"Day.4", 3}, {"Day.5", 4}, {"Day.6", 5}, {"Day.7", 6}},
        UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"Start Date", "End Date"}, "Attribute", "Value"),
        DayOffsetColumn = Table.AddColumn(UnpivotedColumns, "Day Offset", each List.First(List.Select(DayList, (current) => current{0} = [Attribute])){1}),
        ActiveDaysColumn = Table.AddColumn(DayOffsetColumn, "Active Days", each [Start Date] + #duration([Day Offset], 0, 0, 0)),
        RemovedOtherColumns = Table.SelectColumns(ActiveDaysColumn,{"Active Days"})
    in
        RemovedOtherColumns

    Friday, March 17, 2017 2:37 PM
  • Hi Colin, thanks for the reply. This gets part of the way there! I think the missing parts are due to me not defining the question well.

    Currently, for a single row input as follows:

    Start.Date=17/03/17

    End.Date = 31/03/17

    Day.1=1

    Day.2 = 2

    Day.3=null, .. , Day.7=null

    Your solution outputs:

    17/03/17

    18/03/17

    The output I'm looking for is:

    20/03/17

    21/03/17

    27/03/17

    28/03/17

    Differences to resolve are:

    -The code gives first x dates only (where x corresponds to the number of 'active' weekdays in DayList).

    -Day.1 corresponds to Mondays (and Day.2 to Tuesdays etc...) rather than the first day in the period.

    I'm really stuck with this so any further help you can offer would be much appreciated!

    Thanks

    Friday, March 17, 2017 3:07 PM
  • Is the Day.7 value 0 or 7?
    Friday, March 17, 2017 3:51 PM
  • Possible values for Day.7 are:

    7 ('active')

    null (not 'active').

    Friday, March 17, 2017 4:01 PM
  • let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
        UnpivotedColumns = Table.UnpivotOtherColumns(ChangedType, {"Start Date", "End Date"}, "Attribute", "Value"),
        TransformedValues = Table.TransformColumns(UnpivotedColumns, {"Value", each if _ <> 7 then _ else 0}),
        AddedAllDates = Table.AddColumn(TransformedValues, "All Dates", each List.Dates([Start Date], Number.From([End Date]) - Number.From([Start Date]) + 1, #duration(1, 0, 0, 0))),
        AddedActiveDates = Table.AddColumn(AddedAllDates, "Active Dates", each List.Select([All Dates], (current) => Date.DayOfWeek(current) = [Value])),
        ExpandedActiveDates = Table.ExpandListColumn(AddedActiveDates, "Active Dates"),
        RemovedOtherColumns = Table.SelectColumns(ExpandedActiveDates,{"Active Dates"}),
        SortedDates = Table.Sort(RemovedOtherColumns,{{"Active Dates", Order.Ascending}})
    in
        SortedDates

    • Marked as answer by jl8103 Friday, March 17, 2017 4:24 PM
    Friday, March 17, 2017 4:17 PM
  • Amazing, thanks Colin!!
    Friday, March 17, 2017 4:25 PM
  • If you are a lazy coder, this code might appeal to you as well:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        ListOfAllDates = Table.AddColumn(Source, "ListOfDates", each List.Transform({Number.From([Start.Date])..Number.From([End.Date])}, each Date.From(_))),
        Result = Table.AddColumn(ListOfAllDates, "Result", each List.Select([ListOfDates], (x) =>List.Contains(Record.FieldValues(_), Date.DayOfWeek(x)+1)))
    in
        Result

    This returns one list per row - which may be what's needed in some cases.


    Imke Feldmann TheBIccountant.com

    Friday, March 17, 2017 9:29 PM
  • If you are a lazy coder, this code might appeal to you as well

    Lazy, or efficient? :) Anyway, retaining the columns with nulls does save a few steps, and your solution is a very good one. My solution emphasized removing null values up front at the expense of extra steps. It would be interesting to test the performance difference with a large data set...

    By the way, your code returns incorrect results, but is easily fixed:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        ListOfAllDates = Table.AddColumn(Source, "ListOfDates", each List.Transform({Number.From([Start Date])..Number.From([End Date])}, Date.From)),
        TransformedDay7 = Table.TransformColumns(ListOfAllDates, {"Day.7", each if _ <> 7 then _ else 0}),
        Result = Table.AddColumn(TransformedDay7, "Result", each List.Select([ListOfDates], (x) =>List.Contains(Record.FieldValues(_), Date.DayOfWeek(x)))),
        ExpandedResult = Table.ExpandListColumn(Result, "Result"),
        RemovedOtherColumns = Table.SelectColumns(ExpandedResult,{"Result"})
    in
        RemovedOtherColumns

    If you have a better way of handling day 7 without the extra step, let me know. Also, it's not clear why you added 1 to Date.DayOfWeek(x).

    Saturday, March 18, 2017 1:03 PM
  • Thx Colin,

    my added 1 was an attempt to replace your day 7-operation - but I got it wrong ;-)

    So this would be my suggestion then:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        ListOfAllDates = Table.AddColumn(Source, "ListOfDates", each List.Transform({Number.From([Start.Date])..Number.From([End.Date])}, each Date.From(_))),
        Result = Table.AddColumn(ListOfAllDates, "Result", each List.Select([ListOfDates], (x) =>List.Contains(List.ReplaceMatchingItems(Record.FieldValues(_), {{7,0}}), Date.DayOfWeek(x)))),
        #"Removed Other Columns" = Table.SelectColumns(Result,{"Result"}),
        #"Expanded Result" = Table.ExpandListColumn(#"Removed Other Columns", "Result")
    in
        #"Expanded Result"


    Imke Feldmann TheBIccountant.com

    Saturday, March 18, 2017 3:49 PM