How to Create a Dataset in Power BI

Before you can build any eye-catching dashboards in Power BI, you need a solid foundation: a dataset. It's the engine that powers every chart, table, and KPI you create. This guide will walk you through exactly how to create a Power BI dataset, from connecting your first data source to modeling it for accurate and insightful analysis.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Power BI Dataset, Exactly?

Think of a Power BI dataset as more than just a table of raw data. It's the complete package that you build your reports on. A dataset is a collection of one or more tables of data that have been imported, cleaned up, linked together, and enhanced with calculations. Essentially, it includes:

• Data Connections: The links to your original data sources, like an Excel file, a SQL database, or a SaaS application.
• Transformed Data: The raw data after it has been cleaned and prepared for analysis using the Power Query Editor.
• Data Model: The relationships you define between different tables, which allows you to analyze them together (e.g., connecting a Sales table to a Products table).
• Calculations: Custom metrics written in DAX (Data Analysis Expressions), such as creating a "Total Revenue" measure or a "Profit Margin" calculated column.

Building a quality dataset is the most critical part of the Power BI process. A poorly constructed dataset leads to inaccurate reports and headaches down the line, while a well-structured one makes building dashboards intuitive and reliable.

Step 1: Connecting to Your Data Source

Your journey begins in Power BI Desktop. The first step is to point Power BI to where your data lives. Power BI can connect to a massive range of sources, from simple flat files to complex cloud databases.

From the Home ribbon, click on the Get data button. This opens a menu with the most common data sources. If you don't see yours, click More... to open the full browser.

Importing from a File (Excel or CSV)

For many marketers and analysts, data often starts in a spreadsheet. This is the most straightforward connection type.

1. Click Get data > Excel workbook (or Text/CSV).
2. Navigate to your file's location and select it. Click Open.
3. The Navigator window will appear, showing you the sheets and tables available in the file. Select the checkboxes next to the items you want to import. A preview will appear on the right.
4. At the bottom right, you'll see two options: Load and Transform Data.

Let's choose Transform Data to move to the next crucial step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Connecting to an Online Service (e.g., Google Analytics, Salesforce)

Connecting to a cloud application is similar, but it requires authentication.

1. Click Get data > More... and search for the service you want, such as "Salesforce" or "Google Analytics".
2. Select the connector and click Connect.
3. Power BI will prompt you to sign in to your account for that service. Follow the authentication steps.
4. Once connected, the Navigator window will appear, similar to connecting a file. It will show the various data tables and reports you can pull from that service. For example, in Google Analytics, you might see tables for users, sessions, pageviews, and goals.
5. Select the data you need and click Transform Data.

Step 2: Shaping and Cleaning Your Data in Power Query

Welcome to the Power Query Editor. This is where you transform messy real-world data into clean, analysis-ready tables. Almost no dataset is perfect from the start. You might have empty cells, incorrect data types, extra columns, or spelling errors that will throw off your analysis if left unchecked.

Power Query records every cleaning step you perform. You can see this list in the Applied Steps panel on the right. This is incredibly helpful because it allows you to undo steps or see exactly how the data was transformed, creating a repeatable process.

Common Data Cleaning Tasks:

• Remove Columns: Your source file might have dozens of columns you don't need for your report. To simplify your model, right-click the header of a column you don't need and select Remove.
• Change Data Types: Power BI is good at guessing data types, but sometimes it gets it wrong. A column of dates might be imported as text, or numbers as text. Click the icon on the column header (e.g., ABC for text, 123 for whole number) to change its type to the correct one, like Date, Currency, or Decimal Number. Incorrect data types prevent calculations from working correctly.
• Handle Errors or Nulls: Empty cells (nulls) or errors can break your visuals. You can handle them by right-clicking a column header and choosing Replace Values to find nulls and replace them with something else (like 0), or Remove Errors to delete problematic rows entirely.
• Filter Rows: You might only need a subset of the data. For example, you might want to filter out test orders or data from a specific region. Use the filter arrow on a column header, just like in Excel, to include or exclude values.
• Promote First Row as Headers: Sometimes when you import a file, the column headers end up as the first row of data. You can fix this easily with a single click from the Home ribbon: Use First Row as Headers.

Once you are satisfied with your cleaned data, click the Close & Apply button in the top-left corner. This saves your cleaning steps, closes the Power Query Editor, and loads the transformed data into your model.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Building Your Data Model

If you've imported more than one table, you need to tell Power BI how they relate to each other. This is called data modeling, and it's what enables you to create visuals that combine information from different sources.

For example, if you have a Sales table with transaction details and a Customers table with customer information, you need to create a relationship between them using a common field, like CustomerID.

To do this, navigate to the Model View by clicking the icon on the left-hand sidebar (it looks like three connected boxes).

Here you'll see a diagram of all the tables in your dataset.

• Power BI will often try to automatically detect relationships based on column names. If it finds a CustomerID column in both your Sales and Customers tables, it will likely create a line between them for you.
• If you need to create one manually, simply click and drag the common field from one table and drop it onto the corresponding field in the other table. For example, drag CustomerID from the Sales table onto CustomerID in the Customers table.

Creating these relationships is what allows you to, for instance, put "Customer Name" from the Customers table on a chart with "Total Sales Amount" from the Sales table and have it work correctly.

Step 4: Enhancing Your Dataset with DAX (Optional but Powerful)

DAX (Data Analysis Expressions) is Power BI's formula language. It allows you to create new information from your existing data, much like formulas in Excel, but far more powerful.

You can start simply in a couple of ways:

1. Calculated Columns

A calculated column adds a new column to one of your tables. The value in each row is calculated based on other data in that same row. This is useful for static values.

Example: You have a Unit Price column and a Unit Cost column, but no Profit column.

1. Go to the Data View (the grid icon on the left).
2. Select your Sales table.
3. From the Table Tools ribbon, click New Column.
4. In the formula bar, type the following DAX formula and press Enter:

Profit = 'Sales'[Unit Price] - 'Sales'[Unit Cost]

You now have a brand new Profit column in your table.

2. Measures

Measures are calculations that aggregate data, like a sum, an average, or a count. They aren't stored in your table but are calculated on-the-fly when you add them to a visual. This is the preferred method for most calculations in Power BI.

Example: You want to calculate the total revenue from all your sales.

1. From the Report View, right-click your Sales table in the Data pane on the right.
2. Select New Measure.
3. In the formula bar, type this formula:

Total Revenue = SUM('Sales'[Sales Amount])

You can now drag the "Total Revenue" measure into any chart or KPI card, and it will give you the aggregated result, dynamically updating as you apply filters.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 5: Saving and Publishing Your Dataset

Once your data is connected, cleaned, modeled, and enhanced, your dataset is complete. Now you just need to save and publish it.

• Save Your File: First, save your work locally. Go to File > Save to create a .PBIX file. This file contains your connections, applied steps, data model, and any reports you've built.
• Publish to Power BI Service: To share your reports and dashboards, you need to publish your dataset to the Power BI Service (the cloud-based version of Power BI). From the Home ribbon, click Publish. You will be prompted to select a workspace to publish to.

Once published, the dataset and its associated report will be available in your online workspace. You can now set up a refresh schedule to keep your data current and share your insights with your team.

Final Thoughts

Building a robust Power BI dataset is a methodical process of connecting to data, cleaning it in Power Query, establishing relationships in the data model, and enhancing it with calculations. Investing time in creating a clean, organized, and logical dataset is the single most important thing you can do to ensure your reports are accurate, insightful, and easy to maintain.

As you can see, working with BI tools like Power BI requires a significant upfront investment in learning the software’s unique workflow, cleaning tools, and even formula languages. We created Graphed to dramatically shorten this time-consuming process. Instead of manually connecting sources, clicking through Power Query wizards, and debugging relationships, you can simply describe the dashboard you want in plain English. Graphed connects your sales and marketing platforms automatically, handles the data modeling behind the scenes, and builds you a live, real-time dashboard in seconds, allowing you to focus on insights, not setup.