How to Create a Database in Power BI

While Power BI is a fantastic tool for creating dashboards, it doesn't actually build databases in the way a tool like MySQL or SQL Server does. Instead, it lets you build something even more powerful for business analytics: a robust, interconnected data model. This article will show you exactly how to pull data from different sources, clean it up, and connect it all together to create the perfect foundation for your reports.

What Exactly is a Power BI "Database"?

When most people search for how to create a database in Power BI, what they're really looking to do is build a data model. Think of it like a smart, mini-database that lives right inside your Power BI file. It consists of multiple tables of data that are linked together by relationships, allowing you to slice, dice, and analyze information from different sources at once.

Here’s why it's so powerful:

It holds all your data: You can pull data from Excel files, cloud applications, and real databases into one centralized model.
It's interconnected: You can create relationships between tables. For example, you can connect your sales data to your product inventory to see which products are your top sellers.
It's lightning fast: Because it operates in-memory, queries and calculations are incredibly quick, making your dashboards feel snappy and responsive.
It’s flexible: You can add calculations, create custom metrics, and structure the data precisely for the reports you need to build.

Building this model is the most important step in creating a great Power BI report. Let's walk through how to do it.

Step 1: Get Your Data into Power BI

Your data model is useless without data. Power BI can connect to hundreds of different data sources, from simple spreadsheets to complex enterprise data warehouses. For this example, let's assume we have two separate data sources: an Excel file with sales transactions and a CSV file with product information.

Follow these steps to import your data:

Open a new report in Power BI Desktop.
On the Home ribbon, click the Get Data dropdown button.
A menu will appear listing the most common data sources. For our sales file, we’ll select Excel workbook.
Navigate to your Excel file and click Open.
The Navigator window will pop up, showing you all the sheets and tables in your workbook. Check the box next to the sheet or table containing your sales data, and then click Load.

Power BI will import the data and the table will appear in the Fields pane on the right. Now, let’s bring in our product information.

Click Get Data again, but this time select Text/CSV.
Find your product CSV file and click Open.
A preview will appear. If it looks correct, click Load.

You now have two separate, isolated tables in your Power BI file. The next step is to clean them up so they're ready to be connected.

Step 2: Clean and Transform Your Data with Power Query

Almost no data is perfect when you first import it. Imported data is often messy, with extra columns, inconsistent formatting, or incorrect data types. Power BI’s built-in tool for fixing these issues is the Power Query Editor. It's where you'll spend a lot of your time building the foundation of your model.

To open the Power Query Editor, click Transform Data on the Home ribbon.

Here are a few essential cleaning tasks you'll often perform:

Remove Unnecessary Columns

Reports run faster and are easier to manage with less data. If your sales table has columns you know you'll never use in a report (like Note, Employee_ID_Legacy, or blank columns), get rid of them.

To do this: Select the column(s) by holding down CTRL and clicking the headers. Right-click any of the selected headers and choose Remove Columns.

Set the Right Data Types

Power BI often tries to guess the data type for each column (Text, Whole Number, Date, etc.), but it doesn't always get it right. An incorrect data type can prevent you from performing calculations or creating relationships later on.

To do this: Click the small icon on the left side of each column header (e.g., ABC for text, 123 for whole number) and select the correct data type from the list. It’s absolutely essential that dates are formatted as a Date or Date/Time, and numbers you'll use for calculations are Decimal or Whole Numbers.

Filter Out Irrelevant Rows

If your dataset contains test entries, cancelled orders, or data from regions you're not analyzing, you can filter them out. This makes your model smaller and your reports more accurate.

To do this: Click the dropdown arrow on the column header you want to filter by - just like you would in Excel - and uncheck the values you want to exclude.

Once you are done with your cleaning steps, click Close & Apply in the top-left corner of the Power Query Editor. Your changes will be loaded into the data model.

Step 3: Build Relationships Between Your Tables

This is where your collection of separate tables transforms into a true database or data model. By creating relationships, you tell Power BI how your tables are related. This allows you to build a visual that shows, for example, your total sales by product category - something that's impossible when the sales and product data are in separate, unconnected tables.

To manage relationships, click on the Model view icon on the left-hand navigation bar. This view shows each of your tables as a box, with columns listed inside.

Let's say our Sales table has a Product ID column, and our Products table also has a Product ID column. This common column is the key we can use to link them.

Here's how to create the relationship:

In the Model view, find the Product ID column in your Products table.
Click and drag the Product ID field from the Products table over to the Product ID field in the Sales table and release the mouse button.
A line will appear connecting the two tables. When you hover over it, you'll see the related columns highlighted.

Power BI has now created a "one-to-many" relationship. This means that for every one product in your Products table, there can be many corresponding transactions in the Sales table. This structure is the backbone of most reporting and allows you to build powerful, cross-table analysis.

Step 4: Enhance Your Model with DAX Calculations

With a clean, connected model in place, you can now add intelligence to it with DAX (Data Analysis Expressions). DAX is a formula language that lets you create powerful calculations in Power BI. You can use it to create two main types of calculations: Calculated Columns and Measures.

Calculated Columns

A calculated column adds a new column to one of your tables. The calculation is performed for every single row and the result is stored within your data model. This is useful for creating static values.

Example: Let's say your Sales table has Quantity and Unit Price columns, but not a Total Revenue column for each transaction. To create it:

Go to the Data view and select your Sales table.
Go to the Table Tools ribbon and click New Column.
The formula bar will appear. Type the following DAX formula and press Enter:

Revenue = Sales[Quantity] * Sales[Unit Price]

A new Revenue column will appear in your Sales table with the calculated value for each row.

Measures

Measures are much more dynamic. A measure is a formula for a calculation that is performed on-the-fly, based on the context of your report. For example, a "Total Revenue" measure will show the overall total revenue in a KPI card but will automatically calculate the revenue for only North America if you click "North America" on a map visual.

Example: Let's create a measure to calculate the total revenue for our entire business.

Go to the Report view. Make sure no visual is selected on the canvas.
On the Home ribbon, click New Measure.
In the formula bar, type the following DAX formula:

Total Revenue = SUM(Sales[Revenue])

You’ll see the new measure, identified by a small calculator icon, appear in the Fields pane. You can now drag this measure onto your report canvas to create visuals that show aggregated revenue.

For most reporting, measures are far more powerful and flexible than calculated columns. Aim to create measures for your key metrics like total sales, average order value, or profit margins.

Final Thoughts

Building a data model in Power BI is all about importing, cleaning, and connecting your fragmented data sources to create a single source of truth for your reporting. By following the process of getting data, tidying it up in Power Query, building relationships, and adding intelligence with DAX, you create a powerful analytic engine ready to answer any business question you throw at it.

While mastering Power BI is an invaluable skill, it can be a steep learning curve. The process of setting up data sources, cleaning them in Power Query, and writing dozens of DAX measures often takes hours away from the real goal: getting insights. If you need answers fast without the lengthy setup, that's exactly why we built Graphed. We connect to all your data sources and allow you to build dashboards by simply describing what you want in plain English, turning a multi-hour modeling process into a 30-second conversation.