none
Create a column of concurrent dates of week commencing RRS feed

  • Question

  • Hi all

    I have a problem where I am trying to introduce a column of dates that is based on the earliest and latest dates in my data set. I want to create the column so it contains concurrent week commencing dates without omissions if there is a date missing in my data set.

    What is currently happening is any missing date in week commencing has a due date met value of 0%.

    So, if for example,

    w/c 01/10/2018 = 20

    w/c 08/10/2018 = 0

    w/c 15/10/2018 =10

    My chart shows that in w/c 08/10/2018 = 5

    I can't add any images in this post because I haven't had the email to verify my account yet, sorry about that.

    I do not want to add rows manually because the data set will be refreshed often, so I would like the missing data to be automatically populated.

    Thank you for reading.

    Tuesday, November 6, 2018 2:35 PM

Answers

  • Hi Dean

    I must admit this is not 100% clear to me but I'm Under the impression you're looking to auto-generate the "Date window" table discussed in that thread.

    Projects table (named Table1) in blue, Ouput table in green (dates in dd/mm/yyyy format):

    Query code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project name", type text}, {"Created At", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Project name] <> null) and ([Created At] <> null)),
        MinDate = Date.StartOfWeek(List.Min(#"Filtered Rows"[Created At]), Day.Monday),
        MaxDate = Date.StartOfWeek(List.Max(#"Filtered Rows"[Created At]), Day.Monday),
        NbWeeks = Duration.Days(MaxDate - MinDate)/7,
        WeeksTable = Table.TransformColumnTypes(Table.FromList(List.Dates(MinDate, NbWeeks+1, #duration(7,0,0,0)), Splitter.SplitByNothing(), {"Project Weeks"}, null, ExtraValues.Error),{{"Project Weeks", type date}})
    in
        WeeksTable

    If this isn't what you want please upload a screenshot of the actual data + expected result

    Tuesday, November 6, 2018 4:53 PM

All replies

  • Hi Dean

    I must admit this is not 100% clear to me but I'm Under the impression you're looking to auto-generate the "Date window" table discussed in that thread.

    Projects table (named Table1) in blue, Ouput table in green (dates in dd/mm/yyyy format):

    Query code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project name", type text}, {"Created At", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Project name] <> null) and ([Created At] <> null)),
        MinDate = Date.StartOfWeek(List.Min(#"Filtered Rows"[Created At]), Day.Monday),
        MaxDate = Date.StartOfWeek(List.Max(#"Filtered Rows"[Created At]), Day.Monday),
        NbWeeks = Duration.Days(MaxDate - MinDate)/7,
        WeeksTable = Table.TransformColumnTypes(Table.FromList(List.Dates(MinDate, NbWeeks+1, #duration(7,0,0,0)), Splitter.SplitByNothing(), {"Project Weeks"}, null, ExtraValues.Error),{{"Project Weeks", type date}})
    in
        WeeksTable

    If this isn't what you want please upload a screenshot of the actual data + expected result

    Tuesday, November 6, 2018 4:53 PM
  • Hi Dean

    Any problem with the above proposal? Works? Doesn't work? Solves your problem?

    Monday, November 12, 2018 3:03 PM
  • Hello Lz._, sorry for the delay. I've been waiting to get my account verified to upload a screenshot of what I would like, but yes, your solution allows me to get the data as I wanted. Thank you very much!
    Monday, November 12, 2018 4:18 PM