A guide to data transformation in Power BI for marketers

Typically, using the raw data the marketing platform provides isn’t enough for in-depth analysis. To build actionable reports, you need to do some prep work and make your data analysis ready. In this article, we’ll discuss different ways you can use Power BI to transform your data.

Skip ahead:

• Power BI’s data transformation features
• 3 data transformation use cases in Power BI for marketers
• Comparing data transformation in Power BI to other tools
• Making data transformation in Power BI easier by using Supermetrics

Power BI’s data transformation features

Data transformation involves performing any logical transformation on existing data and turning it into something new or blending that data in some way, shape, or form. In Power BI, you can clean, reshape, enrich, and standardize your data.

Let’s dive into some of the key features.

Power BI report, data, and model views

The desktop version of Power BI offers three distinct views, each with a different purpose, in your data transformation process. You’ll find these in the left-side panel, as shown in the image below.

Report view

First, you have the report view. This is the final destination, where your transformed data comes to life as reports with tables, charts, and visuals. You can download this as a PDF or share it online with your stakeholders.

Data view

The second one is the data view. This is your playground. You can perform transformations like renaming fields, recategorizing data, and changing data types. You can get your data in the perfect shape for visualization.

Model view

The third is the model view, where the magic of connecting the dots happens. Here, you can establish relationships between different data sources. Imagine you have data from Google Ads and Google Analytics 4. In the model view, you can link them based on a common field like “campaign name” to create a unified dataset for analysis.

Power Query 

The Power Query editor is one of the most powerful features in Power BI. It’s built-in transformation software that allows you to transform data into any shape you want. In addition to visualization, it offers various functions. Many free tools, like Looker Studio, don’t have anything similar.

You can access it from different places in Power BI using the “Transform Data” button, which opens a new window with the Power Query editor.

You can see all your tables in Power Query and perform various transformations—manage columns, change data types, combine data, etc. We’ll show Power Query in action in the use cases section.

DAX

For the more technical users of Power BI, you can also check out DAX (Data Analysis Expressions). It’s a library of functions and operators, like additions (+), subtractions (-), or logical operators (AND, OR), that can be combined to build formulas and expressions.

While Power Query offers a wide range of data transformation capabilities, DAX provides additional benefits and customization. For example: 

• You can create custom calculations that aren’t available in the standard Power Query Editor interface. Imagine you need a new metric that combines elements from existing columns. With DAX, you can define a formula to create this new metric.
• DAX offers advanced filtering and calculation logic beyond the basic options in Power Query Editor. You can create complex filters based on various criteria or perform calculations that involve multiple data points and functions.
• DAX formulas can be dynamic, meaning they can change based on the context of your visualizations. For example, you can create a DAX formula that calculates the average sales for the selected product category.

While DAX is great, it’s not essential for anyone using Power BI. Most of the time, you can get quite far with the default transformation options and Power Query. If you have more advanced use cases, DAX is a valuable tool to master.

There are various online courses and communities where you can learn more about DAX. Check the Microsoft Documentation site for syntax, functions, and examples.

3 data transformation use cases in Power BI for marketers

There are multiple data transformation use cases. We chose three of the most common ones that marketers usually struggle with, where Power BI is the perfect tool to solve them.

Use case 1: Turning URLs into images for creative fatigue monitoring

Marketers need to deliver fresh and engaging ad creatives. But how do you know when your creatives are losing their effectiveness? With a creative fatigue monitoring report, you can identify what works and what doesn’t, allowing you to swap them out for something new.

While creative fatigue monitoring can be done with many tools, Power BI has two distinct advantages: 

• Effortless URL re-categorization: Categorizing thousands of ad URLs in your data set can be a nightmare. Power BI allows you to recategorize these URLs with just a few clicks.
• Scalability for historical data: Say you have two years of creative data stored in a data warehouse. Analyzing this massive dataset wouldn’t be possible in all tools. Power BI lets you seamlessly handle large volumes of data, ensuring a complete historical view of creative performance.

Watch our tutorial video on turning URLs into images.

Use case 2: Cross-channel paid performance analysis

Most marketers run campaigns across multiple channels, such as Facebook, Google Ads, and TikTok. Measuring the success of each channel in isolation only gives you part of the story. This is where cross-channel paid performance analysis comes in. It lets you see:

• How are your campaigns performing on different platforms?
• How should you effectively spend your budget?
• How do the platforms work together to achieve your overall marketing goals?

While you can do this manually, it quickly becomes time-consuming. You can also use a tool like Supermetrics to combine all your data and blend it in Power BI.

See how you can run cross-channel paid performance analysis in Power BI.

Use case 3: Last click attribution

Last-click attribution is a way to assign conversion credit to the ad a user clicked on last before converting. You can combine data from any ad platform with your conversion data from your web analytics tool to build a last-click attribution report within Power BI. 

In the example below, we’ll show you how to combine Google Ads data with conversion data from Google Analytics 4.

Additional resources: Marketing attribution: How to build an attribution model for your business.

Comparing data transformation in Power BI to other tools

Many tools have data transformation capabilities. Let’s see how Power BI’s data transformation compares to other tools.

Power BI vs. other visualization tools

Often, Power BI is first compared with Looker Studio. Looker Studio is a free and relatively user-friendly visualization tool. You can do many different things with it. But, some of it’s data transformation capabilities, like data blending, are trickier.

Power BI has several advantages for data transformation: 

• Powerful transformation capabilities: As we discussed, Power Query provides different options to transform and enhance your data. 
• Flexibility: You can transform your data at different stages in Power BI, from data extraction to within the visualization platform itself. This gives you control over the transformation process based on your specific needs.
• Scalability: Power BI can effectively handle large datasets, especially when combined with a data warehouse. Looker Studio, however, has some row limitations. So, if you’re trying to pull more data than that, the dashboards just won’t load.

Read more about Looker Studio vs. Power BI for data visualization.

Data transformation in Power BI vs. data warehouse

Although Power BI can manage large volumes of data on its own, there are some cases where you might benefit from combining it with a marketing data warehouse.

A marketing data warehouse provides many benefits for managing data. You need some technical knowledge to transform data in a data warehouse. For example, you’re an agency managing reports for multiple clients, and you need to apply the same transformation, for example, a currency conversion, for each of your clients. Compare: 

• If you do this in your data warehouse, you’d just need to do it once and can apply it to each report. 
• If you do it in Power BI, you’d need to create and manage the transformation on a report level for each dashboard individually. 

So, suppose you’re looking for scalable transformation across many clients. In that case, we recommend doing that in your data warehouse and pulling the data to Power BI for visualization.

Although Power BI beats Looker Studio in its data blending capabilities, there are some limitations. It only allows you to create a relationship based on one join key. But often, you might want to join tables and create relationships on more than just one data point. For example, you might want to match the campaign name, account, and date between two datasets, like Google Ads and Google Analytics 4. Building the relationship inside your data warehouse might make sense in this case as it offers more flexibility.

How to make data transformation in Power BI easier with Supermetrics

While Power BI alone is an efficient tool for transforming your data, you might benefit from combining it with a tool like Supermetrics.

Power BI has some limitations regarding the sources it can access natively. If you want to, for example, bring Facebook Ads to Power BI, you need to manually export and import it. Supermetrics can help automate this process by bringing the data directly to Power BI.

You can also benefit from Supermetrics’ data transformation features, such as custom fields and data blending. There are several data transformation use cases for marketers.

Some transformations are better done in Supermetrics: 

• UNION data blending: While a union is relatively simple to perform utilizing the native capabilities of PowerBI, Supermetrics offers an additional layer of flexibility at the individual field level when mapping data sources together, making custom use cases and workflows much easier and more scalable. 
• Custom Fields: The ability to transform individual data points into new metrics and dimensions isn’t unique to Supermetrics. However, the simple UI makes it straightforward for non-technical marketers to transform data. Additionally, since all the transformations happen in one place, your team can also manage and reuse them in different reports.

Additional resources: Data blending in marketing: What it is, how it works, and why you need it.

Some transformations are better done in Power BI:

• Non-UNION data blending: Blending data horizontally (left, right, inner, outer) is something that Power BI handles very well. You can build and manage relationships using a point-and-click UI in the ‘Model’ view. This gives non-technical users the ability to draw insights from blended data. For example,  you can do conversion attribution by blending GA4 data with ad spend data from Google Ads. 
• Data re-categorization: Since you can’t really do this until you visualize your data, re-categorization can only be done on the Power BI side. Marketers can use this functionality to turn Creative URLs back into the images they represent, opening the door for many creative optimization use cases.

Your turn

The best way to put together what you’ve just learned from this article is to practice. Start following the examples and use cases that Jack provided, and add your own adjustments. Another way you can discover more transformations in Power BI is to connect with other Power BI users. Our favorite resources are the Power BI forums and Supermetrics Community.

And if you want to see how Supermetrics helps you improve marketing reporting in Power BI, start a 14-day free trial.