Get Data from Excel/CSV

Power BI · Getting Data

Theory

Excel workbooks and CSV files are the most common starting point. With Get data → Excel workbook, Power BI opens the Navigator and lists every sheet, named table and range in the file; you tick what you need and either Load it or Transform it first. With Text/CSV, Power BI reads the file, guesses the delimiter, and usually adds two automatic steps: Promoted Headers (first row becomes column names) and Changed Type (detects number, text and date columns).

For example, a sales.csv whose first line is OrderID,Region,Amount arrives as Column1, Column2, Column3 at the Source step; after Promote Headers and Change Type it becomes a proper table with numeric OrderID and Amount. Prefer Excel tables (Insert → Table) over plain sheets because their range grows with new rows.

Why we use it

Most business data still lives in spreadsheets and exports. Connecting to the file instead of copy-pasting means the report refreshes when the file changes and every cleaning step is repeatable.

When to use it

Use it for spreadsheets, exports and small reference lists. If many people edit the file, store it in SharePoint or OneDrive so the Service can refresh it without a gateway; for large or shared data, a database is better than files.

How it works

  1. Get data → Text/CSV
  2. Pick the file and check the preview
  3. Does the data need cleaning first? Yes: Click Transform data to open Power Query. No: Click Load.
  4. Confirm headers were promoted and types detected
  5. Close & Apply to load into the model
  6. Table appears in the Data pane

Syntax

Home → Get data → Excel workbook / Text/CSV → Navigator → Load or Transform data

M:
Excel.Workbook(File.Contents(path), null, true)
Csv.Document(File.Contents(path), [Delimiter=",", Encoding=65001])
Table.PromoteHeaders(table, [PromoteAllScalars=true])

Example

let
    Source = Csv.Document(File.Contents("C:\Data\sales.csv"), [Delimiter=",", Encoding=65001]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
        {{"OrderID", Int64.Type}, {"Region", type text}, {"Amount", Int64.Type}})
in
    #"Changed Type"

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic