Save time with a starter file

If you make lots of Power BI data models and aren’t already doing it, a good practice is to make a starter file to use for new models instead of starting from scratch each time.  Although it is pretty easy to build a new file, I figure I save 20 minutes or more by starting with the following things already done/loaded in my starter file.  Along with pre-loading commonly used custom visuals, themes, etc., you can also add images, backgrounds/wallpapers, and even pre-make functional elements like a collapsible slicer panel as demonstrated on Guy In A Cube recently.  A couple other things you might include in your starter file are explained in more detail below.

DAX Date Table

Any model with date columns ought to have a Date table.  You can easily make one in DAX or with an M query, but one I like to use is shown below.  Depending on the model I’m building, I change what is in the Calendar() part, but the rest stays the same.  I usually use it as written and reference the main date column in the data, so that the Date table dynamically adjusts to the range in the data upon refresh.  You can also easily put a fixed date range like “Calendar(Date(2016,1,1), Date(2019,12,31))” or a hybrid approach like “Calendar(Date(Year(Min(Table[DateColumn])),1,1), Date(Year(Max(Table[DateColumn])),12,31)”. The hybrid one goes from the first of earliest year to the last of the latest year.

Date =
ADDCOLUMNS (
    CALENDAR ( MIN ( Table[DateColumn] )MAX ( Table[DateColumn] ) ),
    “DateAsInteger”FORMAT ( [Date], “YYYYMMDD” ),
    “Year”YEAR ( [Date] ),
    “Monthnumber”FORMAT ( [Date], “MM” ),
    “YearMonthnumber”FORMAT ( [Date], “YYYYMM” ),
    “YearMonthShort”FORMAT ( [Date], “YYYYmmm” ),
    “MonthNameShort”FORMAT ( [Date], “mmm” ),
    “MonthNameLong”FORMAT ( [Date], “mmmm” ),
    “WeekNumber”WEEKNUM ( [Date] ),
    “YearWeekNum”YEAR ( [Date] ) & WEEKNUM ( [Date] ),
    “DayOfWeekNumber”WEEKDAY ( [Date] ),
    “DayOfWeek”FORMAT ( [Date], “dddd” ),
    “DayOfWeekShort”FORMAT ( [Date], “ddd” ),
    “Quarter”“Q” & FORMAT ( [Date], “Q” ),
    “YearQuarter”FORMAT ( [Date], “YYYY” ) & “Q”
        & FORMAT ( [Date], “Q” ),
    “Working Day”IF ( WEEKDAY ( [Date] ) = 1 || WEEKDAY ( [Date] ) = 7“N”“Y” )
)


Note that this expression has the columns “Monthnumber”, “YearMonthnumber”, and “WeekNumber” to be used as Sort By Columns to sort the other columns.  How many times have you had to add a Sort By column when you make your first chart in a new model?  Having an already-sorted Date table saves time. Don’t forget to “Mark as Date Table”.

Measures table … with Refresh

Another good practice is the have a table just for all the measures you’ll make so you can find them easily.  Although it is easy to make one with just entering a “1” through “Enter Data”, I like to make a blank query called “1Measure” (so it is the top table in my list of tables) and enter a simple expression to get the datetime of the last refresh, and add it to card on the first/summary report page.  You just enter “=DateTime.LocalNow()”, convert it to a table, rename it to “Last Refreshed”, and change it to a DateTime column.  The M expression is below if you just want to past it into a blank query.

let

    Source = DateTime.LocalNow(),

    #”Converted to Table” = #table(1, {{Source}}),

#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Last Refreshed”}}),

#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Last Refreshed”, type datetime}})

in

    #”Changed Type”

Another option to get the Last Refresh date is the use the DAX expression Now() in a calculated column.  For example, if you make a measures table with Enter Data “1” approach for example, you can simply add a calculated column with “Last Refresh=Now()”, which will also update when data are refreshed. 

Refresh Duration

Hopefully you build models that refresh in seconds and won’t need this last tip, but if your refresh time isn’t great, you can also measure the refresh duration (e.g., if you want to know if it took 10 vs. 30 minutes, and don’t want to watch your computer the whole time).  For that you need a start and finish time.  Because we can’t yet specify the order that refresh queries are executed, you can approach that by adding a condition or a dependency.  

 For example, you can force a query to be evaluated when the refresh starts with an expression like this in your first query step:

= let

    x = DateTime.LocalNow()

in

    if x <> null then x else null

To get the time at the end of refresh, you can either:

Add a Custom Column to the end of your longest query/table with “=DateTime.LocalNow()”, and you get a column full of that value.

Or

Create another blank query and make it conditional to your longest-refresh query with:

= if Table.RowCount(NameOfLongestQuery) > 0 then DateTime.LocalNow() else null

And convert to table, rename, change to datetime format.

To get the duration, just write a measure to get the difference between RefreshStart and RefreshEnd. It isn’t exactly the refresh time but it is pretty close.

I hope this article gave you some ideas on how to make a starter file with your frequently used elements and save some time.

You May Also Like

About the Author: Pat Mahoney

Pat Mahoney is a chemist in Indiana who enjoys teaching others to use Power BI and other tools, and providing pro bono data science help/instruction to nonprofits, schools, etc.

Leave a Reply

Your email address will not be published. Required fields are marked *