Template Files are an Under-Sung Feature

Power BI allows individuals to build solutions that can increase productivity and generate new insights for entire organizations.  Usually that’s done through publishing models/reports/dashboards to the Power BI service, which provides a secure, flexible, and powerful platform.  Another feature of Power BI Desktop that also gives a secure way to help an organization is the use of template files.  Template files are great for situations where everyone has access to a common report (excel or csv export, for example) that is specific to them or their group, but doesn’t need (or shouldn’t have) access to others’ data (compliance or compensation data, for example).  Of course, it is possible to publish the entire dataset and use Row Level Security in the service to make sure people only see their data, but that requires database access, a Power BI gateway, and pro licenses for all (or a premium workspace).  If you have all that, great.  Many times people can’t easily get database access. However, they can still have a great impact in their organization with template files, by building solutions that work with what people do have access to (e.g., export reports).

Template files have been around for a while (April 2016). With a template file, you can share an entire data model (data transformations, integrations, analyses, and visuals) without sharing any data.  The *.pbit file is useless to anyone that doesn’t have a source file in the expected format (or credentials to the source data, if you do make a template file that connects to a database, Sharepoint site, etc.).  Perhaps in your company, everyone can run the XYZ report for themselves or their group.  If so, one employee can build a model from one of those files, and share it with everyone else as a template file (just build the model on your file, and then Save As and choose *.pbit as the output file type). When someone uses your template file, the model will get populated with their data only, saving them time and/or giving them new insights.

Since we don’t all work for the same company (and have a common XYZ report), I had to come up with a different example to share.  One good application of a template file is a hard-drive analysis. We each want similar information about our computer but would have no need for everyone else’s data (and wouldn’t want to share our own).  To generate the “report”, it is possible to generate a text file from the command prompt of any windows computer that contains information about every file on the computer (filename, path, size, etc.).  The command is shown below:

 dir c:\ /s   >c:\export\files.txt

It is just a simple “dir” command to list all files in all subfolders on the C: drive and write the results to the specified path and filename (you can modify the last part to specify your own path and filename; the above assumes you already have a directory called “export”).  Just run the above at the command prompt of your computer, wait a couple minutes and then you’ll be ready to use the template file (it’s a pretty crude model, but it works for demo purposes). Because each of our files is in the same format, all the data transformations, integrations, analyses, etc. will just work.

When you open the template file you can download here, you will be prompted to give the filepath and filename for the text file you generated.  You are also prompted to give the size HD of your computer, and that value is used in a measure for percent full.

Note: A quick way to get the filepath and filename in a single string is to hold shift and right click on the file in Windows Explorer and choose “Copy as Path”.  When you paste it, it will have quotes around it.  This template file accepts the filepath and name with or without the quotes (i.e., the quotes are stripped out with M code).

“C:\export\files.txt”

The template file has a basic summary page that gives the count of files, space by file type, total space consumed over time, etc..  A second page give more detailed info on file sizes, to identify ones for deletion to save space.  A screen shot is below.

I hope this simple example of a template file gives you some ideas for meeting even more unmet needs in your organization with Power BI.

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 *