Duplicate Rows based on values

[Anonymous]

I'd like to duplicate rows when a column has the value "Weekly", for each week from the "Date" to present. 

Event	Recurring	Date
Event A	Weekly	22/09/2020
Event B	No	14/08/2020
Event C	No	15/03/2020

How can I use Power Query to add rows for "Weekly" for each week since the "Date", and have the Date reflect that?

Something like

Event A	Weekly	22/09/2020
Event A	Weekly	29/09/2020
Event A	Weekly	06/10/2020
Event A	Weekly	13/10/2020
Event A	Weekly	20/10/2020
Event B	No	14/08/2020
Event C	No	15/03/2020

[v-jingzhang · Accepted solution]

@Anonymous  Add several steps to the previous post as below:

let
    Source = #table({"Event","Recurring","Date"},{{"Event A","Weekly","9/22/2020"},{"Event B","No","8/14/2020"},{"Event C","No","3/15/2020"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type with Locale", "Custom", each if [Recurring] = "Weekly" then List.Generate(()=>[Date], each _ <= Date.From(DateTime.FixedLocalNow()), each _ + #duration(7,0,0,0)) else null),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Conditional Column", "Custom"),
    #"Added Conditional Column1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Recurring] = "Weekly" then [Custom] else [Date]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Date", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Date"}})
in
    #"Renamed Columns"

Result:

[lbendlin]

let
    Source = #table({"Event","Recurring","Date"},{{"Event A","Weekly","9/22/2020"},{"Event B","No","8/14/2020"},{"Event C","No","3/15/2020"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Recurring] = "Weekly" then List.Generate(()=>[Date], each _ <= Date.From(DateTime.FixedLocalNow()), each _ + #duration(7,0,0,0)) else null),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Conditional Column", "Custom")
in
    #"Expanded Custom"