Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a table with a complete date range between the 1st and last dates in a column in a diff table

Hi

 

I have a table with a column of dates like so

 

Date

01/01/2019

01/01/2019

01/01/2019

02/01/2019

02/01/2019

03/01/2019

05/01/2019

05/01/2019

07/01/2019

 

I want create a table using Power Query that looks at all of the values in this column, finds the first and last, and creates a new table that has a single row for every date in the range, starting with the first date in the above column and finishing with the last date.

 

This will give me a reference table of unique dates in the range, including any dates which do not have entries in my source table.

 

Can someone help me!

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Thanks! I somehow wasnt aware of all the DAX table & column tools, i thought power query was the only way to do this

     

    This is the solution I have gone for :

    Create a table -

     

    Calendar Reference = CALENDAR(FIRSTDATE('Extract'[Date]),LASTDATE('Extract'[Date]))
     
     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You can create a New Table under Modeling with the following code - Unique_Dates = VALUES('Date Data'[Date])

     

    It seems that creating a date table would serve you better as it would solve this particular issue but also allow for the full use of time intelligence functions.

     

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks!

       

      Quick question on this method though, if the source table is updated and the range changes, will the reference data table created with DAX change to reflect the new range? or will it remain static?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        It is dynamic and will include changes/updates to the data table.

        Thanks

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous ,

    take a look at the following M-code:

    // DateTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLpVgdglwjvFxjVK4pXq45ghsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Date", "Dates"}})
in
    #"Renamed Columns"

// Calendar
let
    Source = {Number.From(List.Min(DateTable[Dates]))..Number.From(List.Max(DateTable[Dates]))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Dates"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Dates"}, DateTable, {"Dates"}, "DateTable", JoinKind.FullOuter),
    #"Expanded DateTable" = Table.ExpandTableColumn(#"Merged Queries", "DateTable", {"Dates"}, {"Dates.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded DateTable",{{"Dates.1", "Dates in Calendar"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Dates not in Calendar", each if [Dates in Calendar] = [Dates] then null else [Dates])
in
    #"Added Custom"

     

    Figure

     

    Regards FrankAT