Convert table of status changes by date to end-of-week status records

[AdamM]

My need is to convert a table of status changes for Requests to a dataset that shows the status of each request as of the end of each week. To illustrate:

This is a table populated by a SharePoint workflow that logs Status changes for Requests.

Parent Request ID	Request Status Change ID	Date	Status
1	1	4/4/2018	New (Not Started)
1	2	4/20/2018	In Process
2	3	5/1/2018	New (Not Started)
3	4	5/7/2018	New (Not Started)
3	5	5/10/2018	In Process
2	6	5/17/2018	In Process
4	7	5/17/2018	New (Not Started)
1	8	5/21/2018	Complete

My desired state, preferably using Get & Transform as opposed to DAX, is to arrive at the below table. One row per week for each Request ID, representing the current Status of the Request at the end of each week (Fridays), regardless of whether or not a Status change occurred in that week.

Parent Request ID	Week of Year	Status Date (Fridays)	Status
1	14	4/6/2018	New (Not Started)
1	15	4/13/2018	New (Not Started)
1	16	4/20/2018	In Process
1	17	4/27/2018	In Process
1	18	5/4/2018	In Process
1	19	5/11/2018	In Process
1	20	5/18/2018	In Process
1	21	5/25/2018	Complete
2	18	5/4/2018	New (Not Started)
2	19	5/11/2018	New (Not Started)
2	20	5/18/2018	In Process
2	21	5/25/2018	In Process
3	19	5/11/2018	In Process
3	20	5/18/2018	In Process
3	21	5/25/2018	In Process
4	20	5/18/2018	New (Not Started)
4	21	5/25/2018	New (Not Started)

My apologies to the community for the vague title. My challenge is that I am not knowledgeable enough with advanced transformation steps to characterize the specific M functions needed here.

Thank you!

AdamM

[ImkeF]

Hi Adam,

please paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDE10gMrQAMv1SyxU0/PJLFIJLEotKUlM0lWJ1IAqNgNhE38gAiMAqPfMUAoryk1OLi8FKQNLGILNMdQ0M8ZkFUmQCVWhOSKEpRKGhgS4uO81AqvQNzXE4C2STOZoS3H60AKs0MoSpdM7PLchJLUlVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Parent Request ID" = _t, #"Request Status Change ID" = _t, Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Request ID", Int64.Type}, {"Request Status Change ID", Int64.Type}, {"Date", type date}, {"Status", type text}}),
    PartitionOnParentRequestID = Table.Group(#"Changed Type", {"Parent Request ID"}, {{"All", each _, type table}}),
    AddListOfAllDates = Table.AddColumn(PartitionOnParentRequestID, "List", each { Number.From(List.Min([All][Date])) .. Number.From(Date.From(DateTime.LocalNow())) }),
    TransformToTableAndDates = Table.AddColumn(AddListOfAllDates, "Date", each Table.FromRows(List.Transform([List], (ListItem) => {Date.From(ListItem)}))),
    JoinWithSource = Table.AddColumn(TransformToTableAndDates, "Join", each Table.NestedJoin([Date], {"Column1"}, [All], {"Date"}, "StatusChanges",1)),
    Cleanup = Table.SelectColumns(JoinWithSource,{"Join"}),
    Expand = Table.ExpandTableColumn(Cleanup, "Join", {"Column1", "StatusChanges"}, {"Column1", "StatusChanges"}),
    ExpandSource = Table.ExpandTableColumn(Expand, "StatusChanges", {"Parent Request ID", "Request Status Change ID", "Date", "Status"}, {"Parent Request ID", "Request Status Change ID", "Date", "Status"}),
    FillDown = Table.FillDown(ExpandSource,{"Parent Request ID", "Request Status Change ID", "Date", "Status"}),
    IdentifyFridays = Table.AddColumn(FillDown, "Status Date (Fridays)", each if Date.DayOfWeek([Column1]) = 6 then [Column1] else null),
    FilterOnlyFridays = Table.SelectRows(IdentifyFridays, each ([#"Status Date (Fridays)"] <> null)),
    AddWeek = Table.AddColumn(FilterOnlyFridays, "Week of Year", each Date.WeekOfYear([#"Status Date (Fridays)"]))
in
    AddWeek

[ImkeF · Accepted solution]

Just recognized that you wanted dates in the future as well. So I've extended the dates until the end of week in this code. For the Friday, you might need to change the number in step "IdentifyFridays", if your local settings start at a different date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDE10gMrQAMv1SyxU0/PJLFIJLEotKUlM0lWJ1IAqNgNhE38gAiMAqPfMUAoryk1OLi8FKQNLGILNMdQ0M8ZkFUmQCVWhOSKEpRKGhgS4uO81AqvQNzXE4C2STOZoS3H60AKs0MoSpdM7PLchJLUlVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Parent Request ID" = _t, #"Request Status Change ID" = _t, Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Request ID", Int64.Type}, {"Request Status Change ID", Int64.Type}, {"Date", type date}, {"Status", type text}}),
    PartitionOnParentRequestID = Table.Group(#"Changed Type", {"Parent Request ID"}, {{"All", each _, type table}}),
    AddListOfAllDates = Table.AddColumn(PartitionOnParentRequestID, "List", each { Number.From(List.Min([All][Date])) .. Number.From(Date.EndOfWeek(Date.From(DateTime.LocalNow()))) }),
    TransformToTableAndDates = Table.AddColumn(AddListOfAllDates, "Date", each Table.FromRows(List.Transform([List], (ListItem) => {Date.From(ListItem)}))),
    JoinWithSource = Table.AddColumn(TransformToTableAndDates, "Join", each Table.NestedJoin([Date], {"Column1"}, [All], {"Date"}, "StatusChanges",1)),
    Cleanup = Table.SelectColumns(JoinWithSource,{"Join"}),
    Expand = Table.ExpandTableColumn(Cleanup, "Join", {"Column1", "StatusChanges"}, {"Column1", "StatusChanges"}),
    ExpandSource = Table.ExpandTableColumn(Expand, "StatusChanges", {"Parent Request ID", "Request Status Change ID", "Date", "Status"}, {"Parent Request ID", "Request Status Change ID", "Date", "Status"}),
    FillDown = Table.FillDown(ExpandSource,{"Parent Request ID", "Request Status Change ID", "Date", "Status"}),
    IdentifyFridays = Table.AddColumn(FillDown, "Status Date (Fridays)", each if Date.DayOfWeek([Column1]) = 5 then [Column1] else null),
    FilterOnlyFridays = Table.SelectRows(IdentifyFridays, each ([#"Status Date (Fridays)"] <> null)),
    AddWeek = Table.AddColumn(FilterOnlyFridays, "Week of Year", each Date.WeekOfYear([#"Status Date (Fridays)"]))
in
    AddWeek

[AdamM]

Absolutely brilliant! Thank you ImkeF!