How to create an Active User Date Table

[mjana]

Hi, 

How can I create a table of only two columns: UserID (type text) and ActivePeriod (type date) from another table like this one? where active users have a null value for their EndDate, like user A00126 in this table.

UserID	StartDate	EndDate
A00123	6/7/2023	6/29/2023
A00124	5/12/2022	6/3/2023
A00125	3/4/2022	null
A00126	6/6/2021	10/10/2022

The new table should have a row for each period (referenced as the Saturday of the week) from StartDate to EndDate for each users. For example user A00123 should have

UserID	ActivePeriod
A00123	6/10/2023
A00123	6/17/2023
A00123	6/24/2023
A00123	7/1/2023

I can use use DateTime.FixedLocalNow() for the EndDate of active users. I just can't figure out how to create the table for each users.

Thanks,

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", type text}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([StartDate])..Number.From(if [EndDate]=null then DateTime.Date(DateTime.LocalNow()) else [EndDate])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
    Filter = Table.SelectRows(#"Removed Columns", each Date.DayOfWeekName([Date])="Saturday")
in
    Filter

Hope this helps.

[mjana]

Thank you so much Ashish!

I liked your solution alot because the code was simple and easy to read. Plus I really appreciated your quick response!!
I wanted to share my modifications. I added two columns SartDatePeriod and EndDatePeriod to help me go through each steps, and finaly decided to keep them.

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", type text}, {"StartDate", type date}, 
{"EndDate", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "StartDatePeriod", each Date.EndOfWeek([StartDate], 
Day.Saturday)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "EndDatePeriod", each if [EndDate] = null 
then Date.EndOfWeek(DateTime.Date(DateTime.LocalNow()), Day.Saturday) else Date.EndOfWeek([EndDate], Day.Saturday)),
#"Added Custom" = Table.AddColumn(#"Added Custom2", "Date", each {Number.From([StartDatePeriod])
..Number.From([EndDatePeriod])}),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
Filter = Table.SelectRows(#"Removed Columns", each Date.DayOfWeekName([Date])="Saturday")
in
Filter

Thanks again

🙂

[Ashish_Mathur]

You are welcome.

[danextian]

hi @mjana,

Please try this. The Added Custom1 step has the formula.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY27CcAwDAV3UW3Qz1ZImTmMdvMsnsxREIkJqDnuHuodLiIWnWMOKGB4oNCGcj4MXrKsqRqyhJJkMiT9tS2dYt3TL7D3j0XAiUx4XyzAfQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, StartDate = _t, EndDate = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"EndDate"}),
    #"Parsed Date" = Table.TransformColumns(#"Replaced Value",{{"StartDate", each Date.From(_, "en-us"), type date}, {"EndDate", each Date.From(_, "en-us"), type date}}),
    #"Added Custom1" = Table.AddColumn(#"Parsed Date", "Dates", each let 
x = [StartDate],
EndforBlank = Date.From(DateTime.LocalNow()),
NewEndDate = if [EndDate] = null then EndforBlank else [EndDate], 
DayOfWeek = Date.DayOfWeek(x),
ActivePeriodStart = Date.AddDays(x, 6-DayOfWeek),
WeeksCount = Number.RoundDown( Duration.Days( NewEndDate - ActivePeriodStart)/7,0) + 2,
Days = List.Dates(ActivePeriodStart, WeeksCount, #duration(7,0,0,0))
in Days, type list),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Dates", each ([UserID] = "A00123  ")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Dates", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Dates]), type text)
in
    #"Inserted Day Name"

[ppm1]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMDQyVtJRMtM31zcygDKNLCHsWB2oChMFBaCEqb6hEUjCCMwz0zdGU2UKFjfWN0EoyivNyUEoMINqNAMpMARzDA30gQikXik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"UserID  " = _t, StartDate = _t, EndDate = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"EndDate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform({Number.From([#"StartDate"])..Number.From([EndDate])??Number.From(Date.From(DateTime.LocalNow()))}, each Date.From(_) )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"UserID  ", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
    #"Changed Type1"

Pat