Ravindra BagaleCourses & study guides मराठी Track your progress

10. Power Query in Excel

10.1 Opening Power Query: Data from a Workbook or CSV

By Last updated:

Steps in Excel

  1. From a Table in this workbook: click inside tblOrders › Data › Get & Transform Data › From Table/Range.
  2. From another workbook: Data › Get Data › From File › From Workbook › pick the file › in the Navigator select a sheet or Table › Transform Data.
  3. From a CSV: Data › Get Data › From File › From Text/CSV › check File Origin (65001: Unicode UTF-8 for ₹ and Marathi text) and Delimiter › Transform Data.
  4. The Power Query Editor opens: queries on the left, a data preview in the middle, Query Settings › Applied Steps on the right, and the formula bar showing the M code of the selected step (View › Formula Bar if hidden).
  5. When done: Home › Close & Load.

Worked example. Shahrukh receives blinkit_orders_nov.csv daily from the Pune cluster. He loads it via From Text/CSV, and Power Query automatically adds the steps Source, Promoted Headers and Changed Type.

Ravindra Bagale's Tip

Power Query's automatic Changed Type step reads dates in US format – 03-11-2026 becomes 11 March! Many students don't notice this. Right-click the date column › Change Type › Using Locale… › choose Date and English (India). After every import, always check the dates and amounts once.

Worked example — opening power query data from a workbook or csv

Why

Priya Deshmukh in Pune and Oliver Bennett abroad both need a clear, repeatable way to use opening power query data from a workbook or csv on a BrightLedger Finance sheet without breaking totals.

What

Opening Power Query Data From A Workbook Or Csv is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for opening power query data from a workbook or csv.
  2. Enter a small made-up value for Priya Deshmukh using $ where money appears.
  3. Add a second value for Oliver Bennett using £ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for BrightLedger Finance's sample row.
Meaning: Once opening power query data from a workbook or csv is correct on one row, copy the same pattern down the column.

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of opening power query data from a workbook or csv on a blank sheet for NovaCraft Tools.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Neha Shah and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Yuki Tanaka.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply opening power query data from a workbook or csv to a 5-row made-up NovaCraft Tools table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with opening power query data from a workbook or csv.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as NovaCraftTo_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Rows in the import (Easy): How many rows are in the Order ID column?
  2. Clean one customer name (Easy): Remove the extra spaces from the first Customer name.
  3. Fix the case of a city (Easy): Show the first City in normal case, like "Pune".
  4. Total quantity (Easy): Add up the Qty column.
  5. How many spaces were extra? (Medium): How many extra spaces does the first Customer name have?
  6. Duplicate order check (Medium): How many times does Order ID "ORD-502" appear?
  7. Orders from one city, any case (Medium): How many rows have City "Kolhapur", in any case?
  8. Amount by channel (Medium): Add up the Amount column for rows where Channel is "App".
  9. Average order amount (Medium): Find the average of the Amount column, rounded to 0 decimals.
  10. Rows after Remove Duplicates (Hard): How many rows are left after the extra copies of "ORD-502" are removed?
  11. Biggest store order (Hard): What is the largest Amount where Channel is "Store"?
  12. Price per unit (Hard): For the first row, divide Amount by Qty.

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Opening Power Query Data From A Workbook Or Csv?
Card 1: What is Opening Power Query Data From A Workbook Or Csv? · Download PNG
Revision card 2: Tiny example — Opening Power Query Data From A Workbook Or Csv
Card 2: Tiny example — Opening Power Query Data From A Workbook Or Csv · Download PNG
Revision card 3: Checklist — Opening Power Query Data From A Workbook Or Csv
Card 3: Checklist — Opening Power Query Data From A Workbook Or Csv · Download PNG