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

[peterver]

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

[FrankAT]

Hi @peterver ,

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

[Anonymous]

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

[peterver]

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]

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

Thanks

[peterver · Accepted solution]

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]))

[Anonymous]

I missed the part about including every date in the range (and not just the values in your fact table) so this is a better solution.

This solution essentially creates a date table as I suggested in my original reply.

Thanks