Power BI for marketing: How to set up your dashboards

If you’re used to running your marketing dashboards on Excel or Google Sheets, Power BI can seem pretty intimidating. 

But don’t let the complexity put you off. With a few simple steps to set your data up the right way, Power BI can be a marketer’s best friend. Its easy analytics tools, customizable visualizations, and ability to handle large data volumes make it the ideal platform for many marketing teams. 

In this article, we’ll break down: 

• The pros and cons of using Power BI for marketing
• Connect your data to Power BI
• Transform your data with the Power Query Editor
• Data modeling in Power BI
• Design the dashboard layout
• Test and publish the dashboard

Power BI for marketing: Pros and cons

Microsoft Power BI is a powerful data visualization and reporting platform. You can use it to import, bring together, and report on data from multiple sources, making it a great option for marketing teams who need insight into cross-channel campaigns and other complex data setups. 

Power BI: The pros 

If you’re already using Microsoft Excel, then Power BI is the natural choice when your data grows too big or complex to handle in a spreadsheet. 

For marketers specifically, some of the biggest advantages of Power BI are: 

• It can handle large volumes of data from multiple sources — ideal for marketers wanting to bring together data from different channels and campaigns 
• It makes creating visually appealing dashboards and reports easy, so you can share insights with your clients or colleagues. 
• You can also make custom visuals for Power BI that present the data exactly how you want. 
• It comes with image recognition and text analytics tools, so you can access and analyze that data without manually entering it. 
• It plays nicely with other Microsoft tools, so you can easily send data in and out of Excel, for instance — handy if that’s a tool you’re already familiar with. 
• It lets you ask questions about your data in natural language, using the “Power BI Q&A” feature. 

Power BI: The cons 

Of course, no data analytics tool is perfect. Power BI comes with a few disadvantages for marketers: 

• It can be a bit tricky to learn, especially when you start diving into data modeling and using Data Analysis Expressions (DAX) to analyze and transform your data. 
• The free version is pretty limited — for instance, you probably won’t be able to handle large data volumes and you’re restricted on who you can share reports with. 
• It’s Microsoft — which can be a pro or a con, depending on the rest of your tech stack and hardware. 
• There are some challenges with integrating data sources. While anything Microsoft shouldn’t be a problem, some integrations will need a bit of tweaking (or a tool like Supermetrics to do it for you.) 

How to connect your data to Power BI

Before you connect all the data to Power BI, you might need to clean it up a bit — the rule with any data analytics is always “Garbage in, garbage out.” 

For example: 

• Check that you’ve used standardized naming conventions for your ad campaigns.  If you’re calling the same campaign Campaign_1 in Facebook and Campaign_Q1_2024 in Instagram, then you’re going to end up with confused, messy data. 
• Standardize formats (like date or currency formats) to ensure that they’re consistent across sources. As in, use the same date format (e.g. YYYY-MM-DD).
• Standardize categorical data (e.g. “Yes” vs. “Y”). 
• Remove irrelevant data (columns and rows that you don’t need for your analysis) to reduce processing time and keep reports focused. 
• Standardize data across sources. If you’ve got data from multiple sources, ensure the categories, units, and formats match. For example, if you have sales data from two countries, convert the currencies to the same unit. 

Importing the data in Power BI 

This is the most popular method of connecting your data. By “import”, we mean you bring the data into Power BI manually, so you can work with it in the platform. 

If you go to the Home tab, you’ll see options for where you can get data from: 

The “Get data” dropdown will open additional choices of data sources. 

We’d recommend importing the data to Power BI if: 

• Your data doesn’t change very often. Because you’ve brought the data into Power BI, you won’t have access to real-time changes to the data — so, if your data changes quickly, then importing isn’t the best choice. (Note: you can refresh your data in Power BI, so you’ll just need to wait for the data to update.) 
• You’re not working with a lot of data. Large data imports can slow down Power BI. 

 You can also link an Excel sheet directly to Power BI and import the data that way. 

Using DirectQuery

This means that you’ll connect Power BI to the data, so you can work with it and use it in Power BI, but you leave the data stored where it is. 

For instance, if you have the data stored in a database like Azure SQL, you can select that as the data source, and then choose DirectQuery. 

This option works best if: 

• You need real-time information. If you want to analyze data that changes fast, then this might be a better option than importing. 
• You want to work with a lot of marketing data. DirectQuery can handle more data volume than importing.
• Your data source is one of those supported by Power BI. For example, Power BI currently doesn’t have native connectors for major marketing platforms such as Facebook, Instagram, X, or other social media data sources, so to pull data from these sources, you’d have to use an API or a third-party data connector like Supermetrics. 

Live Connection via Analysis Services 

If you’re using Analysis Services, a type of data engine for business intelligence—options include SQL Server Analysis Services and Azure Analysis Services. Live Connection also lets you link directly to the data, in special Analysis Services databases designed for analysis. Again, it doesn’t pull the data into Power BI; it just displays visuals based on data directly from Analysis Services.

By this point, you’ve probably realized the catch with Power BI — connecting your marketing data is kinda tricky. 

There aren’t any native connectors for the main sources of marketing data, like social media platforms. Power BI does have a connector for GA4 data, but it’s not exactly user-friendly unless you’re very tech-savvy.  

You’re either looking at doing a lot of manual connections, or spending a long time figuring out APIs and third-party connectors. 

Connecting your marketing data to Power BI via Supermetrics 

This is where Supermetrics comes in. If you use Supermetrics for Power BI, you can connect all your marketing data sources to Power BI without being any kind of data wizard. 

Supermetrics for Power BI lets you pull all your marketing data into Power BI — whether you want to bring Google Analytics data to Power BI, or data from Facebook Ads, Salesforce, HubSpot, and so on.

And it’s super quick and simple to set up — no coding or other technical skills required.

Here’s a quick comparison of connecting Facebook Ads data to Power BI with and without Supermetrics, just so you’ll see what we mean.

To import Facebook Ads data to Power BI manually, you’ll need to: 

• Go to your Meta Ads manager, navigate to “Ads”, and then select the ad sets or campaigns you want to analyze. 
• Click the “Export” dropdown menu and choose a file type — let’s say a CSV file. 
• Go into Power BI, click “Get data”, choose “Text/CSV,” and upload the Facebook ad set data file.  

It’s all pretty straightforward — but it’s a pain because it’s all manual. If you want to change the data you’re working with in the future, you’re going to have to repeat the entire process. 

Plus, presumably, you want to look at more marketing data than just one Facebook Ad set — not to mention all the other ad data (and other data) you want to analyze and report on. 

And, of course, the data is all now static — meaning that your report will be out of date before you’ve even finished working on it. 

Compare that with working with Supermetrics for Power BI. This time, the process goes like this: 

• Log into the Supermetrics Hub. Select “Power BI” as the destination for your data. 
• Click “Create a new query” and select Facebook ads as the data source. 
• Create the query — that means picking the metrics and how you want to look at them. You can preview the data to make sure you’ve got the right information. 
• In Power BI, click “Get Data.” Select Supermetrics from the list. (Remember to use the same email account you used to sign into Supermetrics.) 
• Then just choose the query under Facebook Ads. 

Again, it’s all very straightforward, but there are some major differences: 

• The data isn’t static —it’s updated automatically every time you run the query. 
• You can connect all your different data sources the same way, meaning that you can pull data from lots of different marketing channels into the same report really quickly and easily. 
• You can reuse the query you just created for other reports, saving you a ton of time. 
• You can also tidy up, clean, and blend the data before you load it into Power BI, meaning that you’re not wasting computing power on data you aren’t interested in and your data is clean and reliable.

📖 To learn more about using Supermetrics for Power BI, watch this 10-minute Supermetrics for Power BI tutorial on YouTube. 

Transform your data with the Power Query Editor

So, now you’ve got your marketing data hooked up to Power BI. But, if you want to turn the raw information into actionable reports, you’re going to need to work with the data a bit to turn it into something usable. 

Before you start modeling your data, you can use the Power Query Editor to clean and transform your data before bringing it into your data model – like a mini workspace that helps you get data ready for analysis.

For example, let’s say you have a Sales data file with unnecessary columns, inconsistent date formats, and some blank rows. Here’s how you might use Power Query Editor:

1. Import Data: Connect to the Sales data source and open it in the Power Query Editor.
2. Transform Data:
   • Remove unwanted columns.
   • Convert the date column into a consistent format.
   • Filter out rows with blank values in critical columns.
3. Rename Columns: Give columns more meaningful names, like renaming “Amt” to “Sales Amount.”
4. Apply and Load: Once all transformations are complete, load the data into Power BI.

📖 For more detailed insights into transforming your data in Power BI, check out our guide to data transformation in Power BI. 

Modeling your data in Power BI

If you go into the desktop version of Power BI, you’ll see you have four different “views” or ways of looking at your data: 

• The Report view, where you can visualize your data in tables, charts and other visuals, and share those visuals with other people. 
• The Data view, where you can tidy up your data and get it ready for visualization, for example by renaming fields or changing data types. 
• The Model view, where you can establish relationships between different data sources. 
• The DAX query view, where you can create custom calculations using DAX formulas to add advanced measures and calculated columns.

Let’s turn our attention to the model view—because you’re likely going to have to do some data modeling before you can create those high-impact Power BI reports. 

Data modeling is the process of structuring your data to help Power BI understand how it all fits together. 

When your data comes into Power BI, it’s organized into tables, a bit like an Excel sheet. You need to connect those tables using relationships, which tell Power BI how the tables relate to one another. 

For example, let’s say you have a Sales table and a Products table. They both contain a column with the Product ID. By linking the Product ID in the Sales table to the Product ID in the Products table, Power BI can make connections across these two tables.  

Without that connection, Power BI only sees sales numbers but doesn’t “know” what products those sales are for. With the Product ID relationship, Power BI can pull details from the Products table to add meaning to each sale. For example, instead of just seeing that 100 units were sold, you can see that 100 smartphones or 100 headphones were sold.

As well as connecting the tables, you need to explain what type of relationship they have. In this case, you have a one-to-many relationship. One product could be sold multiple times, so the product ID could appear many times on the sales table. 

Note: One-to-many is usually the best option. The alternatives become more complicated and can accidentally inflate your numbers. 

In Power BI, setting up this relationship is straightforward. In the Model view, you can:

• Define the relationship: Drag and drop the Product ID from the Sales table to the Product ID in the Products table to form a relationship.
• Set the relationship type: This is a many-to-one relationship, meaning that each product in the Products table coud appear multiple times in the Sales table (because it got sold multiple times.) 
• Analyze: Now that the tables are connected, you can create visualizations that pull in data from both table.

Using star schemas in Power BI 

In general, the star schema is usually the best way to model the data relationships in Power BI. It’s called a “star” schema because it looks a bit like a star. 

You have a central table, called the Fact table, which is the main table for the model. Then you have other tables, called Dimension tables, radiating out like the points of a star. The Dimension tables give more information about each entry in the Fact table. 

Using star schemas in Power BI works really well because they make it easier for Power BI to understand and process your data. That way, your reports load faster. Plus, your data is organized, so it’s easier to compare and slice up. And, because the Dimension table only connects to the Fact table, not to each other, your models stay simple, and you don’t run into as many errors. 

Using DAX (Data Analysis Expressions)

DAX is a formula language in Power BI that allows you to make more complex calculations.  DAX is essentially a library of functions and operators, like additions (+), subtractions (-), or logical operators (AND, OR). You can combine these functions to build formulas and expressions, so you can do more complicated calculations that you can’t do with the standard Power Query Editor interface. 

For example, if you wanted to add a new Profit column to your Sales table, you could use DAX to subtract Cost from Revenue for each row.

You don’t need to be an expert in DAX to start, but some basic DAX knowledge can help you create better marketing reports. Recently, Power BI added a DAX visual editor, which makes it much easier to use for people who have little experience with DAX. 

That said, while DAX is definitely useful, you don’t need to have it down to use Power BI. Most of the time, you can get quite far with the default transformation options and Power Query.  

📖 If you’re DAX-curious, Microsoft has a really thorough set of DAX tutorials on their website. 

Design the dashboard layout

OK, so you’ve got your data tidied up (transformed) and set up the relationships between your data (modeling). It’s time to set up your visualizations to show the marketing data effectively. 

Power BI comes with a wide range of charts and graphs: line and bar charts for trends, funnel charts for customer journeys, pie charts for channel distribution, and so on. If you’ve used Excel, much of this will seem pretty familiar. 

The difference is in the level of sophistication you can add to your visuals: 

• Slicers —  These are visual tools that let you filter data in reports interactively. Slicers work a bit like a dashboard control panel, so viewers can choose specific data points to focus on. All the connected visuals on the report page will update to only show that information. 
• Drill-down — Power BI lets you click down into more detailed views of the data through your visuals.

• Customized visuals — Power BI’s visualization tools are very flexible, so you can show the data in ways that make more sense for your specific dashboard. For instance, let’s say you wanted to show marketing expenditure vs. income. You could use a grouped bar chart (a standard option in Power BI). But maybe you’d rather use a tornado chart like the one below, because it clearly shows how income and expenditure patterns have evolved over time. 

For more tips on visualizing data in Power BI, check out our guide to custom visuals for Power BI. 

• Conditional formatting — Power BI’s visuals comes with conditional formatting options so you can showcase key trends or areas of concern, such as low-performing campaigns or high-cost channels. 

Power BI reporting templates

If you want to save yourself a lot of time, marketing reporting templates are a great way to go. Here are a couple of free templates we made for you to use with Power BI: 

• Google Analytics 4 template for Power BI: Analyze your channel, page, and campaign performance using this GA4 Power BI template.

• Google Ads reporting template for Power BI: Measure the success of your Google Ads campaigns easily with this versatile Power BI template. Track basic metrics such as impressions, cost, and CPC, or get audience insights with one click.

Test and publish the dashboard

You’re ready to test out and publish the dashboard. You should check: 

• Do all visuals look right? 
• Do all the data connections and calculations work correctly? 
• Do your slicers and interactive elements work the way you expected? 
• Is your data up to date? 

Once you’ve checked all the features in your dashboard, you’re ready to publish and share it with your teammates or clients. You can also set up automated refreshes to keep your data up to date. 

Supermetrics makes it easier for marketers to use Power BI 

Power BI is a powerful tool for marketing analytics. It can help marketing teams view complex data, analyze it in real time, and share insights through interactive dashboards.

Combined with Supermetrics, connecting and transforming data from multiple marketing platforms becomes a seamless process—no manual exports or complex setups are required.