Date.AddDays Loop

[Qotsa]

Hi,

I have 4 columns, 'day, 'startdate', 'enddate', 'week'.

Col 'day' is a whole number defining the day number.

Col 'week' defines how often the record recurrs.

Below, the 1st record id 11232 states that the record occurs every Mon, Tues, Wed (day = 123) with a startdate 22/07/2019 and end date 09/10/2022 and recurs every 2 weeks(col 'week' = 2). The value in week can be 0 which means record does not recurr, 1 means it recurs every week, etc....

My desired output would show below dates until it meets the enddate.

I'm thinking a Date.AddDays loop in query editor would be the way to go but am struggling how to write it.

[Qotsa]

@v-yueyunzh-msft I need to expand 'End List' column. To do this I have tried to add the below line into the advanced editor without any luck.

#"Added Custom4" = Table.TransformColumns(#"Added Custom3", {{"End List", each if _ = false then {false} else _}})

Could you have a look at the .pbix please?

onedrive .pbix file 

TY

[Qotsa]

@v-yueyunzh-msft No error returned with your M code. Thank you very much for your great help on this.

[Qotsa]

@v-yueyunzh-msft I am just not getting it and am more confused than ever. Maybe I could provide a sample pbix?

[v-yueyunzh-msft]

Hi, @Qotsa 

Thanks for your quick response, the last two days were weekend so i can not response you immediately.

For your question, can you connect your data source from Mysql and make the return table like this:

Then you can give me all the screenshots of your Power Query Editor interface(I want to see the column whether is the same as me ), including your fields and the steps you generated.

And you need to copy the whole M code in the "Advanced Editor" of this query in Power Query Editor to me.

If you can, you can also upload the. pbix file to OneDrive and share your link with me.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Qotsa]

@v-yueyunzh-msft TY for your asistance to date. 

My Advanced Editor has the below code.

let
Source = MySQL.Database("34.252.51.71", "powerbi114", [ReturnSingleDatabase=true]),
powerbi114_cmCarerAvailability = Source{[Schema="powerbi114",Item="cmCarerAvailability"]}[Data],
#"Replaced Value" = Table.ReplaceValue(powerbi114_cmCarerAvailability,",","",Replacer.ReplaceText,{"day"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"day", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"created", "createdBy", "deleted", "deletedBy", "updated", "updatedBy", "companyId", "locationId", "adjustedStartDate", "startTime", "endTime"}),
#"Added Custom" = Table.AddColumn(
test, "start_Monday", (x)=>Date.AddDays(Date.StartOfWeek(x[startDate]),1)

),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Accumul_list", (x)=>
if x[week]=0 then null else if x[week]=1 then {0..(Duration.Days(Date.AddDays(Date.EndOfWeek(x[endDate]),1)-x[start_Monday])+1)/7-1}else if x[week]=2 then
List.Generate(()=>0,each _<=(Number.RoundUp((Duration.Days(Date.AddDays(Date.EndOfWeek(x[endDate]),1)-x[start_Monday])+1)/7/2)-1)*2,each _+2) else 1),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Need List", (x)=>
if x[Accumul_list]<> null then List.Sort(List.TransformMany(x[day_list],each x[Accumul_list],(x,y)=>y*7+x)) else x[endDate]
),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "End List", (x)=>
try
List.Select(List.Accumulate(x[Need List],{},(a,b)=> a& {Date.AddDays(Date.From(x[start_Monday]) ,b)} ),(z)=>z<=x[endDate])

otherwise x[Need List]

)
in
#"Added Custom3"

PBIX -> https://1drv.ms/u/s!AiX0_cACeSxJgSqVYOQ7GmGUY8FG?e=dH6kJG

[v-yueyunzh-msft · Accepted solution]

Hi , @Qotsa 

Thanks for your quick response!

 I cannot connect to your data source, so you need to test in your side .

According to your M code provided, you can try to use this M code to put in the "Advanced Editor":

let
Source = MySQL.Database("34.252.51.71", "powerbi114", [ReturnSingleDatabase=true]),
powerbi114_cmCarerAvailability = Source{[Schema="powerbi114",Item="cmCarerAvailability"]}[Data],
#"Replaced Value" = Table.ReplaceValue(powerbi114_cmCarerAvailability,",","",Replacer.ReplaceText,{"day"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"day", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"created", "createdBy", "deleted", "deletedBy", "updated", "updatedBy", "companyId", "locationId", "adjustedStartDate", "startTime", "endTime"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "start_Monday", (x)=>Date.AddDays(Date.StartOfWeek(x[startDate]),1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Accumul_list", (x)=>
if x[week]=0 then null else if x[week]=1 then {0..(Duration.Days(Date.AddDays(Date.EndOfWeek(x[endDate]),1)-x[start_Monday])+1)/7-1}else if x[week]=2 then
List.Generate(()=>0,each _<=(Number.RoundUp((Duration.Days(Date.AddDays(Date.EndOfWeek(x[endDate]),1)-x[start_Monday])+1)/7/2)-1)*2,each _+2) else 1),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Need List", (x)=>
if x[Accumul_list]<> null then List.Sort(List.TransformMany(x[day_list],each x[Accumul_list],(x,y)=>y*7+x)) else x[endDate]
),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "End List", (x)=>
try
List.Select(List.Accumulate(x[Need List],{},(a,b)=> a& {Date.AddDays(Date.From(x[start_Monday]) ,b)} ),(z)=>z<=x[endDate])
otherwise x[Need List]
)
in
#"Added Custom3"

If this also return the  error code , you can share the error code to me to update this M code.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Qotsa]

@v-yueyunzh-msft I am having difficulty combining the M code with my data source if you could advise what I need to ammend.

[v-yueyunzh-msft]

Hi , @Qotsa 

In my code , the yellow is the step return the table like this:

You need to replace the Source code to your data source and make it return as the same table above.

Then you can add the blue steps under it . 

If your column is different from mine, you can just modify it slightly according to the prompts.

You do not need to define a function to it , you just need to add "," and add the steps i provide under it.And in the last "in" the last step.

Furthermore,the below text is the step name in Power Query Editor , you can see them in the steps!And every step is running under the earlier step.

In Power Query, You can see all of them:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Qotsa]

@v-yueyunzh-msft Thk you. I am unable to review your solution currently as I am travelling. I will be able to later in my day.

Thks again. 

[v-yueyunzh-msft]

Hi , @Qotsa 

According to your description, you want to generate the date list according to the "week, startdate,enddate,day" column . Right?

This is my test data in my side .

You can put this M code in the "Advanced Editor" in your Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY9RDoNACETvst8mwMDichbj/a8hLrUxtv3oDzOQPBi2rYlA0ZamLBGp2Z7VjFOQQ1oJmB6gIJnzti+F6h217usnHBcsTOI32uYuZXg/9UWHv+FBYhc86rIU28UrNDBS++PqIH16/h7597cTLf933mK98u4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, carerId = _t, day = _t, length = _t, startDate = _t, endDate = _t, week = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"carerId", Int64.Type}, {"day", Int64.Type}, {"length", Int64.Type}, {"startDate", type date}, {"endDate", type date}, {"week", Int64.Type}}),
    test = Table.AddColumn(#"Changed Type", "day_list", (x)=> List.Transform(Text.ToList(Text.From(x[day])),(y)=>Number.From(y)-1)    ),
    #"Added Custom" = Table.AddColumn(
test, "start_Monday", (x)=>Date.AddDays(Date.StartOfWeek(x[startDate]),1)                   


),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Accumul_list", (x)=>
if x[week]=0 then null  else if x[week]=1 then {0..(Duration.Days(Date.AddDays(Date.EndOfWeek(x[endDate]),1)-x[start_Monday])+1)/7-1}else if x[week]=2 then 
List.Generate(()=>0,each _<=(Number.RoundUp((Duration.Days(Date.AddDays(Date.EndOfWeek(x[endDate]),1)-x[start_Monday])+1)/7/2)-1)*2,each _+2)   else 1),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Need List", (x)=> 
 if x[Accumul_list]<> null then  List.Sort(List.TransformMany(x[day_list],each x[Accumul_list],(x,y)=>y*7+x)) else x[endDate]
 ),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "End List", (x)=>
try  
List.Select(List.Accumulate(x[Need List],{},(a,b)=> a& {Date.AddDays(Date.From(x[start_Monday]) ,b)} ),(z)=>z<=x[endDate])


otherwise x[Need List]

)
in
    #"Added Custom3"

Then we can get the date list in the added column, other column you do not need , you can delete them .

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly