Forum Discussion

mjana's avatar
mjana
New Member
3 years ago
Solved

How to create an Active User Date Table

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

UserIDActivePeriod
A001236/10/2023
A001236/17/2023
A001236/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,

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

5 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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"

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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's avatar
      mjana
      New Member
      3 years ago

      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

      🙂