Get Data: Excel, CSV & Folders

Every Power BI report starts with Get data. Most beginners' data, and a lot of office data in India, lives in Excel workbooks and CSV exports from billing or accounting software. Often there is one file per month, dropped into a shared folder.

In this lesson you will create the sample files used throughout this tutorial, connect to an Excel workbook, a CSV file and a whole folder, and learn when to choose Load and when to choose Transform Data.

The Kesar Mart sample data

Kesar Mart is an invented chain of four grocery and household stores. Create a folder C:\KesarMart and build these three files so you can follow every lesson.

1. Workbook kesar-mart.xlsx, with two Excel tables (select the range and press Ctrl+T, then name the table on the Table Design tab). Table Stores:

Table Products (CostPrice is what Kesar Mart pays the supplier, in rupees):

2. File sales.csv, exported from the billing software. Paste this into Notepad and save it with UTF-8 encoding. Dates are in day-month-year order, and UnitPrice is the selling price in rupees:

OrderID,OrderDate,StoreID,ProductID,Qty,UnitPrice
1001,05-04-2025,S1,P1,4,650
1002,18-04-2025,S3,P4,2,1299
1003,09-05-2025,S2,P2,10,240
1004,22-05-2025,S4,P3,6,399
1005,14-06-2025,S1,P5,3,799
1006,27-06-2025,S3,P1,5,650
1007,12-03-2026,S2,P4,1,1299
1008,28-03-2026,S4,P2,8,240
1009,03-04-2026,S1,P4,2,1299
1010,16-04-2026,S2,P1,6,650
1011,25-04-2026,S3,P3,4,399
1012,08-05-2026,S4,P5,2,799
1013,19-05-2026,S1,P2,12,240
1014,30-05-2026,S3,P4,3,1299
1015,11-06-2026,S2,P3,5,399
1016,24-06-2026,S4,P1,3,650

3. Folder C:\KesarMart\Monthly, used later in this lesson. Put three CSV files in it with the same heading row: sales-2026-04.csv (orders 1009 to 1011), sales-2026-05.csv (1012 to 1014) and sales-2026-06.csv (1015 and 1016).

Connecting to an Excel workbook

1. On the Home ribbon, select Get data > Excel workbook and open kesar-mart.xlsx.
2. The Navigator window lists everything Power BI found. Excel tables have a table icon; whole sheets have a sheet icon. Tick the Stores and Products tables.
3. Select Load to bring them straight into the model, or Transform Data to open the Power Query Editor first.

Prefer Excel tables to sheets. A table has exactly one heading row and grows automatically when rows are added, so the next refresh picks up new data. A sheet may include titles, notes and blank rows that you then have to clean.

Behind the scenes, Power Query records the connection in M. If you open Transform Data and then Home > Advanced Editor for Stores, you see something like this:

let
    Source = Excel.Workbook(File.Contents("C:\KesarMart\kesar-mart.xlsx"), null, true),
    Stores_Table = Source{[Item = "Stores", Kind = "Table"]}[Data],
    ChangedType = Table.TransformColumnTypes(
        Stores_Table,
        {{"StoreID", type text}, {"StoreName", type text}, {"City", type text},
         {"State", type text}, {"Region", type text}}
    )
in
    ChangedType

Each line is a step: open the file, pick the Stores table, set the column types. The let ... in block returns the last step.

Connecting to a CSV file

1. Select Get data > Text/CSV and open sales.csv.
2. The preview dialog shows three settings: File Origin (the encoding; 65001: Unicode (UTF-8) is right for files that contain ₹ or Hindi text), Delimiter (comma) and Data Type Detection (based on the first 200 rows by default).
3. Select Transform Data, not Load, because the dates need attention.

The generated query looks like this:

let
    Source = Csv.Document(
        File.Contents("C:\KesarMart\sales.csv"),
        [Delimiter = ",", Columns = 6, Encoding = 65001, QuoteStyle = QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    ChangedType = Table.TransformColumnTypes(
        PromotedHeaders,
        {{"OrderID", Int64.Type}, {"OrderDate", type date}, {"StoreID", type text},
         {"ProductID", type text}, {"Qty", Int64.Type}, {"UnitPrice", Int64.Type}}
    )
in
    ChangedType

Table.PromoteHeaders turns the first row into column names. The automatic type step guesses OrderDate is a date. Whether 05-04-2025 becomes 5 April or 4 May depends on the locale; if you set the import locale to English (India) in Install Power BI Desktop, it is read correctly. The next lesson on Power Query shows how to make the step itself locale-proof.

Rename the query to Sales (right-click it in the Queries list), then select Close & Apply.

Connecting to a whole folder

When a new file arrives every month, connecting to each file separately is painful. The Folder connector combines every file in a folder into one table, and the next refresh includes any new file automatically.

1. Select Get data > Folder, enter C:\KesarMart\Monthly, and select OK.
2. A list of files appears with columns such as Name, Extension and Date modified. Select Combine & Transform Data.
3. Power BI asks you to confirm a sample file; it uses that file to work out the columns and the steps, then applies the same steps to every file.

Power BI adds helper queries (a sample file, a parameter and a function named Transform File) in a folder in the Queries list. Leave them alone; they make the combine work. The final query also adds a Source.Name column so you know which file each row came from:

Eight rows in total: three from April, three from May and two from June. In July, drop sales-2026-07.csv into the folder and select Refresh; nothing else changes.

Load or Transform Data?

When in doubt, choose Transform Data. Checking takes a minute; fixing a report built on wrong data takes much longer. File sources are always imported, meaning a copy of the data is stored in the .pbix file and updated on refresh. Databases also offer DirectQuery, covered in Get Data from SQL Databases.

Common mistakes

• Choosing the sheet instead of the Excel table. You get titles and blank rows mixed into the data. Pick the table.
• Moving or renaming the source file. Refresh fails with a "could not find" error. Update the path in File > Options and settings > Data source settings > Change Source.
• Wrong File Origin. Rupee symbols and Hindi names appear as strange characters. Choose 65001 (UTF-8).
• Different columns in monthly files. The folder combine silently drops or misplaces columns. Agree a fixed export format with whoever sends the files.

Key points

• Get data connects Power BI to Excel workbooks, CSV and text files, folders, databases and many online services.
• In the Navigator, prefer Excel tables over sheets.
• The CSV preview lets you set encoding (use UTF-8), delimiter and type detection.
• The Folder connector combines files with the same structure and adds new files on refresh; filter on the file extension first.
• Choose Transform Data whenever anything needs checking; every step is recorded in M.

CsvOnly = Table.SelectRows(Source, each Text.Lower([Extension]) = ".csv")