Data source and dataset in Power BI - Step-by-Step Guide

Introduction

This feature helps you connect your Power BI report to the original data location and manage the data you use in your report. It solves the problem of keeping your report data updated and organized.

When you want to connect your report to an Excel file stored on your computer.

When you need to refresh your sales data from a cloud database regularly.

When you want to combine data from multiple sources into one report.

When you want to check or change where your report gets its data from.

When you want to manage the data loaded into your report to improve performance.

Steps

Step 1: Click

- Home tab > Get data button

The Get Data window opens showing different data source options

💡 Use the search box to quickly find your data source type

Step 2: Select

- Get Data window > Choose your data source (e.g., Excel, SQL Server)

A connection window opens to select the specific file or server

💡 Make sure you have access permissions to the data source

Step 3: Browse and select

- File explorer or connection window

The data preview loads showing tables or sheets available

💡 Preview the data to confirm it is the correct source

Step 4: Click

- Load or Transform Data button

The data loads into Power BI as a dataset or opens Power Query Editor for cleaning

💡 Use Transform Data to clean or shape data before loading

Step 5: Open

- Fields pane

You see the dataset tables and fields ready for building visuals

💡 Rename tables or fields for clarity if needed

Step 6: Click

- Home tab > Refresh button

Power BI updates the dataset with the latest data from the source

💡 Schedule refreshes in Power BI Service for automatic updates

Before vs After

Before

Fields pane is empty, no data loaded, report visuals show no data

After

Fields pane shows tables and fields from the connected data source, report visuals can display data

Settings Reference

Data source settings

📍 Home tab > Transform data > Data source settings

Manage and update the connection details to your data source

Default: Current data source path

Refresh settings

📍 Home tab > Refresh dropdown

Update the dataset with the latest data from the source

Default: Manual refresh

Scheduled refresh

📍 Power BI Service > Dataset settings

Automate data updates without manual intervention

Default: No scheduled refresh

Common Mistakes

Selecting the wrong data source type

The connection will fail or load incorrect data

Confirm the data source type matches your actual data location before connecting

Not refreshing the dataset after data changes

Report shows outdated data, causing wrong insights

Click Refresh or set up scheduled refresh to keep data current

Loading too much unnecessary data

Slows down report performance and increases file size

Use Transform Data to filter and select only needed tables and columns

Summary

Data source connects your report to the original data location.

Dataset is the data loaded into Power BI for building visuals.

Keep data updated by refreshing or scheduling refreshes.

Manage connections and data load to keep reports accurate and fast.

Practice

1. What is a data source in Power BI?

easy

A. The original location where data is stored before importing

B. A visual report created in Power BI

C. A chart made from data

D. A filter applied to data

Solution

1. Step 1: Understand the term 'data source'

   A data source is where data originally exists, like a database or file.

2. Step 2: Differentiate from dataset

   A dataset is the data imported into Power BI for analysis, not the original location.

3. Final Answer:

   The original location where data is stored before importing -> Option A

4. Quick Check:

   Data source = original data location [OK]

Hint: Data source is where data lives before Power BI imports it [OK]

Common Mistakes:

• Confusing data source with dataset
• Thinking data source is a report or visualization
• Mixing data source with filters

2. Which of the following is the correct way to add a new dataset in Power BI?

easy

A. Click 'Get Data' and select the data source to load data

B. Create a new report without connecting to any data

C. Use the 'Format' pane to add data

D. Right-click on a visual and select 'Add Dataset'

Solution

1. Step 1: Identify how to load data

   Power BI uses 'Get Data' to connect to data sources and load datasets.

2. Step 2: Eliminate incorrect options

   Creating reports without data or using format pane does not add datasets.

3. Final Answer:

   Click 'Get Data' and select the data source to load data -> Option A

4. Quick Check:

   Loading data = Get Data button [OK]

Hint: Use 'Get Data' to import datasets from sources [OK]

Common Mistakes:

• Trying to add data from the Format pane
• Assuming reports create datasets automatically
• Looking for dataset options in visuals

3. Consider this Power BI M code snippet to load data from a CSV file:

let
  Source = Csv.Document(File.Contents("C:\\Data\\sales.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
  ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}, {"Region", type text}})
in
  ChangedType

What does the Source step represent?

medium

A. A visualization of sales data

B. The dataset after changing column types

C. The original data loaded from the CSV file

D. A filter applied to the sales data

Solution

1. Step 1: Analyze the 'Source' step

   'Source' uses Csv.Document and File.Contents to load raw data from the CSV file.

2. Step 2: Understand subsequent steps

   'ChangedType' modifies data types; 'Source' is before this, so raw data.

3. Final Answer:

   The original data loaded from the CSV file -> Option C

4. Quick Check:

   Source = raw data load step [OK]

Hint: 'Source' usually means raw data load in Power Query [OK]

Common Mistakes:

• Confusing 'Source' with transformed data
• Thinking 'Source' is a visualization
• Assuming 'Source' applies filters

4. You tried to load data from an Excel file in Power BI but got an error. Which of these is the most likely cause?

medium

A. You need to format the data as a table in Power BI

B. You forgot to create a report before loading data

C. Power BI does not support Excel files

D. The file path is incorrect or file is missing

Solution

1. Step 1: Identify common errors loading Excel data

   Incorrect file path or missing file causes load errors.

2. Step 2: Eliminate incorrect reasons

   Power BI supports Excel files; reports are created after loading data; formatting in Power BI is not required to load.

3. Final Answer:

   The file path is incorrect or file is missing -> Option D

4. Quick Check:

   File path errors cause load failures [OK]

Hint: Check file path first if data load fails [OK]

Common Mistakes:

• Thinking Power BI can't load Excel files
• Assuming report creation is needed before data load
• Believing data must be pre-formatted in Power BI

5. You want to combine sales data from two different Excel files into one dataset in Power BI. What is the best approach?

hard

A. Load both files as separate datasets and create a relationship between them

B. Use Power Query to append the two tables into a single dataset

C. Load one file and manually copy data from the other into Power BI

D. Create two reports, one for each file, and merge reports later

Solution

1. Step 1: Understand combining data sources

   Appending tables in Power Query merges data into one dataset for analysis.

2. Step 2: Evaluate other options

   Creating relationships keeps data separate; manual copying is inefficient; merging reports is not practical.

3. Final Answer:

   Use Power Query to append the two tables into a single dataset -> Option B

4. Quick Check:

   Append tables in Power Query to combine datasets [OK]

Hint: Append tables in Power Query to merge datasets [OK]

Common Mistakes:

• Using relationships instead of appending for combined data
• Copy-pasting data manually
• Merging reports instead of datasets