Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mjana
New Member

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,

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

mjana_0-1688838584448.png

Thanks again

🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danextian
Super User
Super User

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"

danextian_0-1688605696089.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
ppm1
Solution Sage
Solution Sage

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"

ppm1_0-1688604505519.png

 

Pat

Microsoft Employee

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.