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
- Get data → Text/CSV
- Pick the file and check the preview
- Does the data need cleaning first? Yes: Click Transform data to open Power Query. No: Click Load.
- Confirm headers were promoted and types detected
- Close & Apply to load into the model
- 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
- Get data → Excel workbook / Text/CSV
- Navigator lists sheets, tables and named ranges
- Promoted Headers and Changed Type are added automatically for CSV
- Refresh breaks if the file path or column names change
- Use Excel tables rather than sheets so new rows are picked up