Power BI Template Creation Guide & Tips - Enterprise DNA

Power BI Template Creation: Guide & Tips

No comments

Today, you will learn how to create your perfect Power BI template file that is configured to your preferences and settings. This will save you 20 to 30 minutes every time you start up a Power BI report and will make Power BI work exactly the way you want it to. You can watch the full video of this tutorial at the bottom of this blog.

Choosing A Color Theme

Let’s start with a color theme. To use a custom Enterprise DNA color theme, go to View and then Browse for themes

Choosing color theme for Power BI

Search for the eDNA.json file and click open to successfully import the theme. 

Choosing Power BI

Creating A Key Measures Table

I always like to put my measures in a measures table. To create a key measures table, click Home and Enter Data.

Creating Key Measures for Power BI

Rename the table in the dialogue box that will appear. Type Key Measures Table in the name field, and click Load. This will open the Fields pane. 

Setting The Date Table

The next step is to create a date table. I always recommend using Melissa de Korte’s extended date table, but I like a particular version that uses a day offset in addition to all the fields she incorporates.

I’ve published this version to the analyst hub, which you can access by heading to External Tools and clicking Analyst Hub. 

Creating Date Table

In the Analyst Hub, go to Community and open EDNA Extended Date Table with DayOffset

Creating Date Table

Open the file and click Copy Code.

Creating Date Table

Then, open Power BI and go to Power Query by clicking Transform Data. 

Creating Date Table

Click New Source and choose Blank Query in the dropdown. Then, open the Advanced Editor. 

Creating Date Table

An initial code will show up. Delete this code and paste the code that you copied. Click done if there is no syntax error.

Creating Date Table

You cannot know exactly what the start and end dates are for every report that you will be doing. So what you can do is create a three-year table starting with the previous year (January 1st), ending the next year (December 31st), and with the fiscal year starting in July. Then, click Invoke. 

You can always extend or shorten the parameters in the formula bar quickly. You simply change it to a date table as shown in the image below. 

Creating Date Table

Even though Power BI has added a NETWORKDAYS function, I recommend the version that Imke Feldmann developed. His version incorporates directly into Power BI as an offset. 

To use this version, go back to the Analyst Hub and go to the Community repository. Search for Imke Feldmann Networkdays Offset Custom Function and open the file. 

Creating Date Table

Copy the code back to Power Query. 

Creating Date Table

Create another blank query by right-clicking on the left side pane and choosing New Query then Blank Query

Creating Date Table

Next, open the Advanced Editor and paste the function.

Rename it to fxNetworkday

Creating Date Table

The next step is to go to your date table, Add Column, and Invoke Custom Function

Creating Date Table

Then invoke fxNetworkday, create a column called Networkday Offset, and click OK. Your Networkday Offset should be good to go. 

Creating Date Table

If you’ve reached this part, you’re making good progress on your Power BI template. To continue, set the Key Measures up as a new group by scrolling to Key Measures, Move to Group, then New Group. For the purpose of this tutorial, call the new group Data Model and click Okay

Creating Date Table

Move the date table (Dates) to the Model. That does it for Power Query, so you can now Close & Apply

Creating Date Table

Your date table should start loading at this point. Always remember to mark your date table by clicking Date, Mark as date table, and selecting our date field as shown in the image below. This is necessary to get your time intelligence functions working properly.

Creating Date Table

Refining The Options

To edit the options, go to the File tab and open Options and settings

Modifying Options for the Power BI Template

There are a number of options that I recommend turning off. I detest the Auto detect new relationships after data is loaded because it creates a ton of problems with the model.

Also, turn off Import relationships from data sources on first load. These options can be found under Current File then Data Load in the left pane.

In the Report settings, check Change default visual interaction from cross highlight to cross filtering and Use modern visual tooltips with drill actions and updated styling

Using External Tools

Power BI features numerous external tools available for use. In this tutorial, you will use some of the custom Enterprise DNA external tools.

After changing the options, the next thing to do is use external tools to further modify your date table. So, go to External Tools and start with the Power Sort Pro

Power Sort Pro

What this tool does is enable Enterprise DNA date table matching. With this tool, if you click Find Sort By Columns and then Set Sort By Columns, it automatically goes through your date table and sets each column to its proper sort column.

You no longer have to do any manual setting aside from the three clicks. A pop-up window saying Finished will show up. 

Using External Tools for Power BI template

The second external tool that you will use is Meta Mechanic Pro. Click on it and set some defaults in your metadata. 

Meta Mechanic Pro

What you need to do is make all your dates in short-date format by clicking the corresponding checkbox. Also, turn off all the aggregations on your numeric fields (whole numbers, double numbers, and decimal numbers).

I recommend doing this because I believe auto-sum just creates a ton of problems. Finally, choose Key Measures in the drop-down under All Measures and check the box as shown in the image. 

Click Get Metadata and Set Metadata. A success window will show up. 

Using External Tools for Power BI template

If you look at your dates table after this step, you will see all the aggregations are turned off and your dates are all in the proper format. It takes a little bit of time for that to update, so you may need to wait for a while. 

Modifying The Data Visualization Pane

You are almost done! One of the last things you need to do is modify the visualization pane.

For example, I think the pie charts do not look good, and I don’t want them on my visualization pane. So, unpin that visual by clicking on the Pie Chart icon, choosing Unpin this visual, and confirming in the resulting window. Also, unpin the Azure Map.

James Dale’s Icon Map is a better map visual, so you will use that as the replacement. 

To do that, click the three dots (…) under the Build visual pane and click Get more visuals

The screen will load and take you to AppSource. Search for Icon Map in the search bar, click on the appropriate result, and then Add.

Data visualization

Once added, you can set it as the default visual by clicking on the Icon Map and then Pin to visualizations pane. After this step, every time you open the file to start off Power BI, it will start off with that visual. 

Creating Blank Button for Power Bi

There are other visuals that you may include in the defaults, such as Deneb, HTML, CSS, and Zebra BI visuals. You can use them as options to configure the pane based on your preferences, but you will not go through them in this tutorial. 

Creating A Blank Button

There are a couple more things to cover to complete your Power BI template. At this point, you will add a blank button to your template so that you can insert your logos. 

Go to Insert, Buttons, and choose Blank in the dropdown. 

Steps in creating a Power BI template

Expand the blank button to the shape of your logo and drag it to the upper right corner. Go to Properties, then under style, choose Custom Icon in the Icon Type. It will give you the option to pull in your logo file by clicking Browse. Search for the logo on your computer and open it. 

Creating Blank Button for Power Bi

Afterward, turn on Action, choose Web URL under Type, and add your URL (e.g., enterprisedna.co). Then, for the Tool Tip, put the Text that you want to show. In the sample image below, you can see that the text says to go to the Enterprise DNA site.

You can add a whole series of buttons such as mailto button or a button to your LinkedIn profile or other social media accounts. What you did earlier is just an example of how you can add a blank button.

Saving Your Power BI Template

The last thing to do is save the file by going to File and clicking Save as. Find the directory and type your desired File Name. In the Save as type options, choose Power BI template files (*pbit) instead of *pbix. Click Save. 

A window showing a Template description will show up. In the sample image below, the description says Basic EDNA theme starter report. That is a good description so simply click Okay. 

Saving the Power BI Template

Using Conductor Pro To Create The Power BI Template

Once done, go to File Explorer. Search for the file that you just saved, right-click, and choose Copy as path. 

Saving the Power BI Template

What you can do is either create a JSON file from scratch or, if you are an Enterprise DNA subscriber, you can use Conductor Pro under the External Tools. I have made an entry named Starter Template, and you just have to click Edit

Saving the Power BI Template

Basically, this creates the template for the JSON file. You can see that you already have a starter template, a description, and the path to Power BI. The only thing to do is to paste the path that you copied into the Arguments box. Keep the window open for now.

Saving the Power BI Template

Creating Power Bi Template Icon

The last step in creating your perfect Power BI template is creating an icon via Base64. This website has good features in terms of image optimization and creating smaller files. Make sure that this feature is Enabled as shown below.

Base64

Find an image that you think fits as an icon for your particular template. Then, drop the file into the Base64 conversion site and wait for the conversion to finish. Once completed, click Copy Image then go back to Conductor Pro

Base64

Paste the copied link in the Icon Data box. Then, scroll to the top of the pasted text and remove the word data  so the text will only start with image. Click Save and you will be prompted with a message saying Successfully edited the tool

Creating Power Bi Template Icon

Close the window and go back to your Power BI template. Then, save the template back to your file using the same name and file type as earlier. Click Replace and Okay.

Creating Power Bi Template Icon

After completing the last step, you can now shut down Power BI and let the changes take effect. You don’t need to save changes because you have already done that.

Your Power BI Template Results

It’s time to view the results of your work! Start by opening your Power BI and going to External Tools. You should see your starter template with your chosen icon. Click on it and that should load all of your settings. 

Starter Template

You should see it creating the date and measures table, inserting the Icon Map and your logo, and everything should be set up and ready to go. The image below shows what your final template should look like. 

Final Power BI Template Appearance

***** Related Links *****
Report Builder In Power BI: Formatting Objects & Visualizations
Power BI Project Planning: Discovery & Ingestion
Overview Of The Query Editor User Interface In Power BI

Conclusion

In this tutorial, you learned how to create your perfect Power BI template. Now, every time you create a new Power BI file, you only have to click the Startup Template and save yourself 20 or 30 minutes. More importantly, you will have the template working exactly as you want it to. You can apply what you’ve learned to tailor the program to your specific needs.

All the best,

Brian Julius

Enterprise DNA Power BI On-Demand

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.