Scenario:
Suppose we have sample data as follows:
If [VisitFreq] = 1 then we will show One row for for [StoreID] with [VisitDates] same as [DateKey]
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
)
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:
If we simply wants to repeat the rows, we can use following Query:
RepeatBlanks
= Table.AddColumn(ChangeType, "RepeatBlanks", each List.Repeat({ },[VisitFreq]))
= Table.AddColumn(ChangeType, "RepeatBlanks", each List.Repeat({ },[VisitFreq]))
RepeatDates
= Table.AddColumn(ChangeType, "RepeatDates", each List.Repeat({[DateKey]},[VisitFreq]))
= 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.