Split two dates into weeks and return the date of the Monday Start of Week

[iss05e]

Hello,

I am quite a novice at BI and so hope there may be some shared expertise on this forum to help me.

I need to take two dates, expand them into weeks and also return the Monday start of week for each row.

So for example, if I have the following:

Placement Start Date	Placement End Date
10 June 2019	28 June 2019

I need to get to this (note: Monday date is in UK format):

Placement Start Date	Placement End Date	Monday Start of Week
10 June 2019	28 June 2019	10/06/2019
10 June 2019	28 June 2019	17/06/2019
10 June 2019	28 June 2019	24/06/2019

I have actually managed to achieve this as follows (albeit probably a long-winded way of doing it):

1) Transform data -> Add Column -> Custom Column -> New column ->  Add formula:

{ Number.From(Date.WeekOfYear([Placement Start Date],Day.Sunday))..Number.From(Date.WeekOfYear([Placement End Date],Day.Sunday)) }

2) Expand to new rows
3) Rename the column to Week Number

4) Add column Monday Start of Week

5) Add formula:

Monday Start of Week = DATE(YEAR([Placement Start Date]),1,-2)-WEEKDAY(DATE(YEAR([Placement Start Date]),1,3))+[Week Number]*7

This works until the dates span multiple years. amd fails on step (2) as BI is trying to span rows from say, week number 50..3 - and so returns null.

Is there a way I return the Monday start of week for each week, regardless how many years the range spans?  Any help much appreciated!

Regards,

Paul

[iss05e · Accepted solution]

Jimmy,

Yes, that does produce the results I'm after! 😀

However, I currently have the existing code in my advanced editor as I've cast some of the date formats from the csv file which is the source:

let
    Source = Csv.Document(File.Contents("C:\Stuff\BI_test\gemma.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Placement Start Date", type text}, {"Placement End Date", type text}, {"Student Code", Int64.Type}, {"Surname", type text}, {"Given Name", type text}, {"Agency Code", type text}, {"Cohort Code  (Student RefId 77)", type text}, {"Agency Name", type text}, {"Agency Type", type text}, {"Group Name", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Placement Start Date", type date}}, "en-US"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Placement End Date", type date}}, "en-US")
in
    #"Changed Type with Locale1"

As the Query Editor is new to me, where would I "knit" the code in that you have suggested?  Apologies if this is a really newbie question .....

Regards,

Paul

[Jimmy801 · Accepted solution]

Hello

try this code

let
    Source = Csv.Document(File.Contents("C:\Stuff\BI_test\gemma.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Placement Start Date", type text}, {"Placement End Date", type text}, {"Student Code", Int64.Type}, {"Surname", type text}, {"Given Name", type text}, {"Agency Code", type text}, {"Cohort Code  (Student RefId 77)", type text}, {"Agency Name", type text}, {"Agency Type", type text}, {"Group Name", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Placement Start Date", type date}}, "en-US"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Placement End Date", type date}}, "en-US"),
    TransformTable = Table.TransformRows
    (
        #"Changed Type with Locale1",
        (row)=>
        let 
            HowMuchDuplicatesNeeded = Number.RoundDown(Duration.TotalDays(row[Placement End Date]-row[Placement Start Date])/7),
            CreateMultipleRows = List.Transform(List.Numbers(0,HowMuchDuplicatesNeeded+1), (weeks)=> Record.AddField(row, "Monday Start of Week", Date.AddDays(row[Placement Start Date], weeks * 7)))
        in 
            CreateMultipleRows
    
    ),
    CreateFinalTable = Table.FromRecords(List.Combine(TransformTable), null,null)
in
    CreateFinalTable

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ziying35]

Hi, @iss05e, try this:

// output
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ8CrNS1UwMjC0VNJRMrJA4sbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Placement Start Date" = _t, #"Placement End Date" = _t]),
    fnGen = (st, en)=>
            let fx= each Date.ToText(_, "dd MMM yyyy","en") 
            in List.RemoveNulls(
                   List.Generate(
                       ()=>st, 
                       each _<en, 
                       each Date.AddDays(_, 1), 
                       each if Date.DayOfWeek(_)=0 then List.Transform({st, en, _}, fx) else null
                   )
               ),
    gen = Table.ToList(Source, each let st=Date.From(_{0}, "en"), en=Date.From(_{1}, "en") in fnGen(st, en)){0},
    result = Table.FromRows(gen, Table.ColumnNames(Source)&{"Monday Start of Week"})
in
    result

[wdx223_Daniel]

@iss05e  here is the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ8CrNS1UwMjC0VNJRMrJA4sbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Placement Start Date" = _t, #"Placement End Date" = _t]),
    Custom1 = let fx=(x)=>Number.From(Date.From(x)) in Table.FromValue({fx(Source[Placement Start Date]{0})..fx(Source[Placement End Date]{0})}),
    Custom2 = Table.Group(Custom1,"Value",{"n",each Source{0}&[Monday Start of Week=Date.ToText(Date.From(List.Min([Value])),"dd MMM yyyy","en")]},0,(x,y)=>Byte.From(Date.DayOfWeek(Date.From(y))=0)),
    Custom3 = Table.FromRecords(Custom2[n])
in
    Custom3

[iss05e]

Jimmy,

If it's any help, my source csv looks as follows:

Placement Start Date,Placement End Date,Student Code,Surname,Given Name,Agency Code,Cohort Code  (Student RefId 77),Agency Name,Agency Type,Group Name
"9/30/2019","12/6/2019","500000000","Test","Student","CH0027","19WX1","Location 1","Community Hospital"
"6/10/2019","6/28/2019","500000000","Test","Student","MTH0051","19WX1","Location 2","Community Hospital"
"12/30/2019","3/13/2020","500000000","Test","Student","MTH0051","19WX1","Location 2","Community Hospital"
"9/7/2020","11/27/2020","500000000","Test","Student","MTH0124","19WX1","Location 3","Community Hospital"
"12/28/2020","3/19/2021","500000000","Test","Student","MTH0090","19WX1","Location 4","Community Hospital"

[iss05e · Accepted solution]

Jimmy,

Yes, that does produce the results I'm after! 😀

However, I currently have the existing code in my advanced editor as I've cast some of the date formats from the csv file which is the source:

let
    Source = Csv.Document(File.Contents("C:\Stuff\BI_test\gemma.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Placement Start Date", type text}, {"Placement End Date", type text}, {"Student Code", Int64.Type}, {"Surname", type text}, {"Given Name", type text}, {"Agency Code", type text}, {"Cohort Code  (Student RefId 77)", type text}, {"Agency Name", type text}, {"Agency Type", type text}, {"Group Name", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Placement Start Date", type date}}, "en-US"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Placement End Date", type date}}, "en-US")
in
    #"Changed Type with Locale1"

As the Query Editor is new to me, where would I "knit" the code in that you have suggested?  Apologies if this is a really newbie question .....

Regards,

Paul

[Jimmy801 · Accepted solution]

Hello

try this code

let
    Source = Csv.Document(File.Contents("C:\Stuff\BI_test\gemma.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Placement Start Date", type text}, {"Placement End Date", type text}, {"Student Code", Int64.Type}, {"Surname", type text}, {"Given Name", type text}, {"Agency Code", type text}, {"Cohort Code  (Student RefId 77)", type text}, {"Agency Name", type text}, {"Agency Type", type text}, {"Group Name", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Placement Start Date", type date}}, "en-US"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Placement End Date", type date}}, "en-US"),
    TransformTable = Table.TransformRows
    (
        #"Changed Type with Locale1",
        (row)=>
        let 
            HowMuchDuplicatesNeeded = Number.RoundDown(Duration.TotalDays(row[Placement End Date]-row[Placement Start Date])/7),
            CreateMultipleRows = List.Transform(List.Numbers(0,HowMuchDuplicatesNeeded+1), (weeks)=> Record.AddField(row, "Monday Start of Week", Date.AddDays(row[Placement Start Date], weeks * 7)))
        in 
            CreateMultipleRows
    
    ),
    CreateFinalTable = Table.FromRecords(List.Combine(TransformTable), null,null)
in
    CreateFinalTable

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[iss05e · Accepted solution]

Thank you all who responded to my plea!  I now have this working.

You guys are brilliant! 😁

Thanks again for taking the time to help me.  It's very much appreciated.

[Jimmy801]

Hello @iss05e 

you can use Table.TransformRows instead. This logic here is calculating the difference between end and start and / 7. A new column is added (Record.AddField) to make the calculation. However its taking the start date and adds 0, 1, 2 etc. weeks. I don't know if you have scenarios where the start date is not a monday, but the new column should be a monday.  If yes, could you make an example how this should look like

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ8CrNS1UwMjC0VNJRMrJA4sbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Placement Start Date" = _t, #"Placement End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Placement Start Date", type date}, {"Placement End Date", type date}}),
    TransformTable = Table.TransformRows
    (
        #"Changed Type",
        (row)=>
        let 
            HowMuchDuplicatesNeeded = Number.RoundDown(Duration.TotalDays(row[Placement End Date]-row[Placement Start Date])/7),
            CreateMultipleRows = List.Transform(List.Numbers(0,HowMuchDuplicatesNeeded+1), (weeks)=> Record.AddField(row, "Monday Start of Week", Date.AddDays(row[Placement Start Date], weeks * 7)))
        in 
            CreateMultipleRows
    
    ),
    CreateFinalTable = Table.FromRecords(List.Combine(TransformTable), null,null)
in
    CreateFinalTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy