Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating Date Tables

As of today (10/26/18), what's the most convenient way to create date tables in BI? Apparently there used to be a feature whereby one only had to click a single button to drop a date table into the m...
Tips and Tricks
  • edhans's avatar
    edhans
    7 years ago

    IMHO, the fastest way is:

    1. Open a blank query in Power Query of Power BI
    2. type ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}
    3. That will generate a series of numbers as a list
    4. Convert it to a table (upper left menu button.
    5. Convert the ABC123 type to date
    6. Rename to Date.
    7. Now you have a date table. Add columns as necessary (year, month, month name, etc) to make your date table suit your needs.
    8. Close and load.
    9. Right-click on it and mark it as a date table.

     

    If you have source data with dates in it, get fancy and find the earliest date in your data, then make row #2 above be Jan 1, YYYY where YYYY is the earliest date in your dataset,

  • edhans's avatar
    edhans
    5 years ago

    So if your original line is like this, you just need to use some functions to determine the dates vs hardcoding.

    ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))}

     

    This will always give you a rolling 6 months. I've inserted a lot of line feeds to make the formulas a bit easier to read, but you could type that Source line all on one line. The key to all of this is DateTime.LocalNow() - that is equivalent to @NOW() in Excel - the current date and time from the system clock.

    let
    Source = 
        {
            Number.From(
                Date.AddMonths(
                    DateTime.Date(
                        DateTime.LocalNow()
                    ),
                    -6
                )
            )..
            Number.From(
                DateTime.Date(
                    DateTime.LocalNow()
                )
            )          
        },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
    #"Changed Type"

      

Harsh_Insights's avatar
Harsh_Insights
Frequent Visitor
10 months ago

Creating a Date Table in Power BI Using Power Query

There are several ways to create a Date Table in Power BI. However, creating it using Power Query is the most efficient and robust approach.

I have developed an M Query formula that you can directly use. You only need to replace the minimum and maximum date source table names in the formula. That’s it — your Date Table will be ready.

Once you have the Date column, you can easily create other time-based columns such as Year, Month, Quarter, and Week from the Transform > Date section.

 

Steps to Quickly Create the Date Table

  1. Create a Blank Query
    Go to:
    Home → New Source → Blank Query
  2. Rename the Blank Query to “DateTable”
    In the Query Settings pane (on the right):
    Name → type DateTable
  3. Paste the M Query Code
    Open the Formula Bar and paste the M Query code below.
    Replace Source[Date] with your actual date column name (usually from your Fact Table).

 

Use the below M Query to Generate Date Tabele:

let
Source = fact_sales_monthly, // Replace with your actual table name
MinDate = Date.From(List.Min(Source[date])), // Replace 'date' with your actual date column name
MaxDate = Date.From(List.Max(Source[date])),
DateList = List.Dates(
MinDate,
Duration.Days(MaxDate - MinDate) + 1,
#duration(1, 0, 0, 0)
),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"})
in
DateTable