Generate dates from weekdays

[maghura]

Hi,

I want to generate dates corresponding to a list of weekdays within a date range. 

Here is my table:

ID  Time      Start Date    End Date     Recurrence
10 09:10:00 29/08/2023 08/09/2023 "Monday","Wednesday","Friday"
11 09:00:00 29/08/2023 29/10/2023 "Monday","Thursday","Friday"
12 10:00:00 23/08/2023 01/11/2023 "Monday","Tuesday","Wednesday","Thursday","Friday"

Here is an example for ID 10 of the output I would like to have:

ID  Time      Start date    End date     Recurrence date
10 09:10:00 29/08/2023 08/09/2023 30/08/2023
10 09:10:00 29/08/2023 08/09/2023 01/09/2023
10 09:10:00 29/08/2023 08/09/2023 04/09/2023
10 09:10:00 29/08/2023 08/09/2023 06/09/2023
10 09:10:00 29/08/2023 08/09/2023 08/09/2023

Thanks

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lGytDI0sDIAsYws9Q0s9I0MjIyBHCDLwBLGiVHyzc9LSayMUdKJUQpPTclLLYbx3IoywUylWB2ggYZgAw2wGQjkGBpgNTAko7QIh3lGQLVg50ENNEZ2oaG+oSF2A0sR7kN1LQ6rYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID " = _t, #"Time " = _t, #"Start Date" = _t, #"End Date" = _t, Recurrence = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", Int64.Type}, {"Time ", type time}, {"Start Date", type date}, {"End Date", type date}, {"Recurrence", type text}}, "fr"),

    #"Recurrence Dates" = Table.ReplaceValue(
        #"Changed Type",
        each [Recurrence],
        each let
            span = List.Dates([Start Date], Duration.TotalDays([End Date]-[Start Date])+1, #duration(1,0,0,0)),
            rec_dt = List.Select(span, (dt) => Text.Contains([Recurrence], Date.DayOfWeekName(dt, "en-US")))
        in rec_dt,
        Replacer.ReplaceValue,
        {"Recurrence"}
    ),
    #"Expanded Recurrence" = Table.ExpandListColumn(#"Recurrence Dates", "Recurrence")
in
    #"Expanded Recurrence"

[Vijay_A_Verma]

See the sample code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lGytDI0sDIAsYwMjIx1DSx0jSzhHEsgH8iJUfLNz0tJrNSJUQpPTclLLQayY5SAPLeiTDBTKVYHaJwh2DgDnMYZGkA4SMaFZJQW4TDNCKgS7DQ044zhxhnqGhiiGVeKcBuqS3FYFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Time = _t, #"Start Date" = _t, #"End Date" = _t, Recurrence = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Time", type time}, {"Start Date", type date}, {"End Date", type date}, {"Recurrence", type text}}),
    #"Added Custom" = Table.RemoveColumns(Table.AddColumn(#"Changed Type", "Recurrence Date", each List.Select(List.Dates([Start Date], Duration.Days([End Date] - [Start Date]) + 1, #duration(1, 0, 0, 0)), (x)=> Text.Contains([Recurrence], Date.DayOfWeekName(x)))), {"Recurrence"}),
    #"Expanded Custom" = Table.TransformColumns(Table.ExpandListColumn(#"Added Custom", "Recurrence Date"), {"Recurrence Date", Date.From})
in
    #"Expanded Custom"