Forum Discussion

Judy101's avatar
Judy101
Frequent Visitor
1 year ago
Solved

Subset of dates from a table using Power query

Looking for Power query that helps list dates in a table from a pre-exisiting table. Conditions: 1. Minimum date should be 1st date of data from 26 months back. Example if today is 3/25/2025, I wan...
General Comment
Need Help
Tips and Tricks
  • pankajnamekar25's avatar
    pankajnamekar25
    1 year ago

    Hello Judy,

     

    You can try below M script

     

    let

        Today = Date.From(DateTime.LocalNow()),

            StartDate = Date.StartOfMonth(Date.AddMonths(Today, -26)),

        DateList = List.Dates(StartDate, Duration.Days(Today - StartDate) + 1, #duration(1,0,0,0)),

        DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error)

    in

        DateTable

     

    Thanks,
    Pankaj

    If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi Judy101 

 

Try this:

let
    //get today's date relative to the user's timezone. Note: PBI Service uses UTC
    TodaysDate = Date.From(DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) + #duration(0,8,0,0))),
    StartDate = Date.AddMonths(TodaysDate,-26),
    //creates a list of numbers from the number of equivalent of StartDate to TodaysDate and then transform the list to dates
    Dates = List.Transform( {Number.From(StartDate)..Number.From(TodaysDate)}, Date.From),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}})
in
    #"Changed Type"

This takes into consideration that user's machine's timezone is different from that of PBI Service.