What is a Power Query Template and why it’s a big deal in the era of Fabric?

If you are in the data analytics realm, Microsoft Fabric is all over the place in the previous months! Fabric introduced “this”, Fabric implemented “that”, “this” integrates seamlessly with Fabric, and so on…New features and functionalities are being incorporated almost on a daily basis and it’s really hard to stay up to date with all the innovations and improvements.

In this “ocean” of innovations, there are certain features that don’t get the deserved limelight – as they somehow go under the radar. Some of them, I really consider “hidden gems” – you might not use them in each and every solution, but in some scenarios, they can be of immense help.

A hidden gem that I’m introducing today is called Power Query Template. As of today, this feature is still in preview (the same as many others in Microsoft Fabric), but this doesn’t minimize its potential.

What is Power Query Template?

When you work with your data in Power Query (and I assume you all opened Power Query Editor at least once, since you are reading Power BI – related blog:)), you define a lot of different things there: for example, queries, parameters, locale settings, etc. Now, imagine being able to pack all of these into one single box, and then being able to reuse the content of that box somewhere else! That’s exactly what Power Query Template enables you to do.

How can I create Power Query Template from Power Query Desktop?

Well, I have bad news – you can’t:(…Power Query Template can be generated only from the Power Query Online experience – meaning, you can only leverage Dataflows (Gen1) to create a template. But, stay tuned, I’ll show you how to create Power Query Template from the existing Power BI semantic models with some Python (sempy) magic…

Create a template from Dataflow Gen1

If you are not sure what Dataflow Gen1 is, don’t worry – I get you covered. Once you open a dataflow, you’ll see the option to Export template:

Once you provide a name for your template, it will be saved as a .pqt file. Then, you need a Fabric capacity and Fabric-enabled workspace to be able to leverage the template data in Fabric.

The item you need is a Dataflow Gen2, which is one of many options to ingest the data into Fabric. Once you create a new Dataflow Gen2, you should see the option to Import from a Power Query Template:

From there, the process is fairly straightforward and once you locate your .pqt file, you’ll see that “everything” is in there – all your transformations, settings, and queries! On top of that, since we are now in Fabric and using a Dataflow Gen2, we can output the data into various destinations, such as lakehouse or warehouse. How cool is that!

Once you publish the Dataflow, it will create a Delta table in the destination (let’s imagine that our destination is a Fabric lakehouse) and from there, anyone who has access to a lakehouse can leverage the data from that Delta table. So, instead of being “caged” in the Dataflow, where only Power BI can consume the data from it, now any downstream workload can potentially benefit from having this data available – you can query it by using SQL, Spark, use it for data science or machine learning tasks, and so on.

This is cool, but let’s be honest – the number of use cases is fairly limited – meaning, the data is not always stored in Dataflow Gen1, and it would be great if we can also be able to create Delta tables out of “regular” Power Query stuff performed in Power BI Desktop and stored in the Power BI semantic model in the workspace.

This time, I have good news:) This is feasible thanks to SemPy library in Python and great work from Michael Kovalsky who provided the script for implementing this approach.

Create Power Query Template from existing Power BI semantic models

If you’re not familiar with Michael Kovalsky‘s work, you are missing a lot:) Michael maintains a fantastic GitHub repo, full of super-useful scripts and solutions. In case you missed it, I already described how to migrate existing Power BI import models to Direct Lake. Here, I just want to show how you can create Delta tables from tables in the existing Power BI model – meaning, you don’t necessarily need to use Direct Lake mode on top of them (you can if you want, of course), but have them available in Delta format within Fabric, so that essentially any Fabric engine can take advantage of them.

The entire process is described within Michael’s GitHub repo, so I’ll focus only on the key steps to bring your Power BI imported tables into OneLake.

The first step is to install the .whl file in Fabric notebook:

%pip install "https://raw.githubusercontent.com/m-kovalsky/fabric_cat_tools/main/fabric_cat_tools-0.4.1-py3-none-any.whl"

Next, install necessary libraries:

import fabric_cat_tools as fct
from fabric_cat_tools.TOM import connect_semantic_model

Now, we are ready to create a .pqt file:

import fabric_cat_tools as fct
fct.create_pqt_file(
            dataset = 'PQT Demo',
            file_name = 'PQT Demo pqt',
            workspace = None
            )

Let’s go now to the Files section of our lakehouse and check if the file is there…

As you may notice, the file was successfully created. The next step is to use a Dataflow Gen2 the same way as we did previously when importing data from Dataflow Gen1.

All my transformation logic applied via Power Query Editor in Power BI Desktop is preserved, so my newly created delta tables in the lakehouse are just “plug-and-play” – I don’t need to repeat same transformation steps and apply my transformation logic from scratch.

I’ll configure the output destination for my tables and publish the Dataflow. After a few minutes, tables that were part of the import semantic model, with all their transformation logic, are now available in the Fabric lakehouse in delta format.

Conclusion

Power Query Template is a very useful feature. Although this definitely should not be your default choice when moving data into Fabric, there are certain scenarios in which your transformation logic already took place in the Power BI semantic model and/or Dataflows Gen1, and reusing this logic makes more sense than recreating everything from scratch.

Thanks for reading!

Last Updated on May 23, 2024 by Nikola

Spread the music: