How to Add additional Rows to a table based on a Condition in Power Query

How to repeat Rows in a table based on a Condition in Power Query
Scenario:

Suppose we have sample data as follows:

From the above sample data, based on [VisitFreq] and [DateKey] Columns, we have to repeat the rows for each [StoreID] with a new Column as [VisitDates] as per below Criteria:

If [VisitFreq] = 1 then we will show One row for for [StoreID] with [VisitDates] same as [DateKey]

If [VisitFreq] = 2 then we will show Two rows for [StoreID] with [VisitDates] as { [DateKey] and ([DateKey] + 7 Days) }

If [VisitFreq] = 3 then we will show Three rows for [StoreID] with [VisitDates] as { [DateKey],  ([DateKey] + 7 Days)  and ([DateKey] + 14 Days) }

If [VisitFreq] = 4 then we will show Four rows for [StoreID] with [VisitDates] as { [DateKey],  ([DateKey] + 7 Days) , ([DateKey] + 14 Days) and ([DateKey] + 21 Days) }

If [VisitFreq] = 5 then we will show Five rows for [StoreID] with [VisitDates] as { [DateKey],  ([DateKey] + 7 Days) , ([DateKey] + 14 Days) , ([DateKey] + 21 Days) and ([DateKey] + 28 Days)  }

Please note that, if the [VisitDates] falls on "Saturday" or "Sunday" then we have to consider the next Date falls on "Monday".

We can achieve the above Scenario using the following way. The below Script returns a new Calculated Column [VisitDates] with list of Dates based on [VisitFreq].

VisitDatesList

= Table.AddColumn(cc_WeekDay, "VisitDates", each 

let 

    /* Visit Frequency */

    vDay1 = [DateKey],

    vDay7 = Date.AddDays([DateKey],7),

    vDay14 = Date.AddDays([DateKey],14),

    vDay21 = Date.AddDays([DateKey],21),

    vDay28 = Date.AddDays([DateKey],28),

/* Week Days: 0-Sunday, 6-Saturday */

    vWeekOfDay1 = Date.DayOfWeek(vDay1),

    vWeekOfDay7 = Date.DayOfWeek(vDay7),

    vWeekOfDay14 = Date.DayOfWeek(vDay14),

    vWeekOfDay21 = Date.DayOfWeek(vDay21),

    vWeekOfDay28 = Date.DayOfWeek(vDay28),

/* Revised Dates */

vRevDay1 = if vWeekOfDay1 = 0 then Date.AddDays(vDay1,1) 

            else if vWeekOfDay1 = 6 then Date.AddDays(vDay1,2) else vDay1,

vRevDay7 = if vWeekOfDay7 = 0 then Date.AddDays(vDay7,1) 

            else if vWeekOfDay7 = 6 then Date.AddDays(vDay7,2) else vDay7,

 vRevDay14 = if vWeekOfDay14 = 0 then Date.AddDays(vDay14,1) 

            else if vWeekOfDay14 = 6 then Date.AddDays(vDay14,2) else vDay14,

vRevDay21 = if vWeekOfDay21 = 0 then Date.AddDays(vDay21,1) 

            else if vWeekOfDay21 = 6 then Date.AddDays(vDay21,2) else vDay21,

vRevDay28 = if vWeekOfDay28 = 0 then Date.AddDays(vDay28,1) 

            else if vWeekOfDay28 = 6 then Date.AddDays(vDay28,2) else vDay28,

    

vRevVisitDate = 

    if [VisitFreq]=1 then {vRevDay1} else if [VisitFreq]=2 then {vRevDay1, vRevDay7} 

    else if [VisitFreq]=3 then {vRevDay1, vRevDay7, vRevDay14 }

    else if [VisitFreq]=4 then {vRevDay1, vRevDay7, vRevDay14, vRevDay21 } 

    else if [VisitFreq]=5 then {vRevDay1, vRevDay7, vRevDay14, vRevDay21,vRevDay28 }        else null

in 

    vRevVisitDate

)

Next Click on "Expand to New Rows" from the [VisitDates] to expand all rows from the Lists.

Result:

Note: 

I have added a new Column as [VisitDayWeek] to verify the Week of the [VisitDates].

-----------------------------------------------------------------------------------------------------------------

Here is the complete Power Query of the above Scenario for your reference:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc3bCQAhDETRXvItmInxVYvYfxuyGwNL9m/gwNy1CFKUEgFZszD42bTTC9WgOohDM2gOxaEbdAd1GAbj15gGM14pG4BjXXEFsaJyRT6ZfQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StoreID = _t, DateKey = _t, VisitFreq = _t]),

ChangeType = 

    Table.TransformColumnTypes(Source,{{"StoreID", Int64.Type}, {"DateKey", type date}, {"VisitFreq", Int64.Type}}),

cc_WeekDay = 

    Table.AddColumn(ChangeType, "WeekDay", each Date.DayOfWeekName([DateKey])),

    

VisitDatesList = Table.AddColumn(cc_WeekDay, "VisitDates", each 

let 

/* Visit Frequency */

    vDay1 = [DateKey],

    vDay7 = Date.AddDays([DateKey],7),

    vDay14 = Date.AddDays([DateKey],14),

    vDay21 = Date.AddDays([DateKey],21),

    vDay28 = Date.AddDays([DateKey],28),

/* Week Days: 0-Sunday, 6-Saturday */

    vWeekOfDay1 = Date.DayOfWeek(vDay1),

    vWeekOfDay7 = Date.DayOfWeek(vDay7),

    vWeekOfDay14 = Date.DayOfWeek(vDay14),

    vWeekOfDay21 = Date.DayOfWeek(vDay21),

    vWeekOfDay28 = Date.DayOfWeek(vDay28),

/* Revised Dates */

    vRevDay1 = if vWeekOfDay1 = 0 then Date.AddDays(vDay1,1) else if vWeekOfDay1 = 6 then Date.AddDays(vDay1,2) else vDay1,

    vRevDay7 = if vWeekOfDay7 = 0 then Date.AddDays(vDay7,1) else if vWeekOfDay7 = 6 then Date.AddDays(vDay7,2) else vDay7,

    vRevDay14 = if vWeekOfDay14 = 0 then Date.AddDays(vDay14,1) else if vWeekOfDay14 = 6 then Date.AddDays(vDay14,2) else vDay14,

    vRevDay21 = if vWeekOfDay21 = 0 then Date.AddDays(vDay21,1) else if vWeekOfDay21 = 6 then Date.AddDays(vDay21,2) else vDay21,

    vRevDay28 = if vWeekOfDay28 = 0 then Date.AddDays(vDay28,1) else if vWeekOfDay28 = 6 then Date.AddDays(vDay28,2) else vDay28,

    

vRevVisitDate = if [VisitFreq]=1 then {vRevDay1} else if [VisitFreq]=2 then {vRevDay1, vRevDay7} 

                    else if [VisitFreq]=3 then {vRevDay1, vRevDay7, vRevDay14 }

                    else if [VisitFreq]=4 then {vRevDay1, vRevDay7, vRevDay14, vRevDay21 } 

                    else if [VisitFreq]=5 then {vRevDay1, vRevDay7, vRevDay14, vRevDay21,vRevDay28 } else null

    in 

    vRevVisitDate

),

    ExpandVisitDates = Table.ExpandListColumn(VisitDatesList, "VisitDates"),

    cc_VisitDayWeek = Table.AddColumn(ExpandVisitDates, "VisitDayWeek", each Date.DayOfWeekName([VisitDates]))

in

    cc_VisitDayWeek

-----------------------------------------------------------------------------------------------------------------

Notes:
If we simply wants to repeat the rows, we can use following Query:

RepeatBlanks
= Table.AddColumn(ChangeType, "RepeatBlanks", each List.Repeat({ },[VisitFreq]))

RepeatDates
= Table.AddColumn(ChangeType, "RepeatDates", each List.Repeat({[DateKey]},[VisitFreq]))

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------