Saturday, November 4, 2023

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])),
    
VisitDatesListTable.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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog