Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date.AddDays Loop

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...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    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

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi , Anonymous 

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