Power Query Tutorial: Clean and Transform Data Faster
📞 +91 8075 400 500 · learn@cokonet.com Contact us
● Advanced Excel

Power Query tutorial: clean and transform data once, then refresh forever.

If you spend every Monday copying the same exports, deleting the same blank rows and fixing the same date formats, Power Query will give you that time back. It records each cleaning step once, then repeats them all with a single Refresh. This Power Query tutorial covers the steps beginners use most, with examples from sales and accounts work in Kerala offices.

Cokonet Academy 16 November 20269 min read
Power Query tutorial banner with a data analyst turning a messy spreadsheet into a clean table on two monitors

What is Power Query in Excel?

Power Query is the data import and transformation tool built into Excel (Data > Get and Transform Data) and Power BI. It connects to files, folders, databases and web sources, lets you clean and reshape data through recorded steps, and loads the result to a table that updates when you click Refresh.

Why Power Query saves hours every week

Want to practise Power Query on real business files? The Advanced Excel course at Cokonet Academy covers Power Query, PivotTables, Power Pivot, dashboards and macros.

Quick answer: Manual cleaning has to be repeated every time new data arrives. Power Query repeats it for you, without formulas breaking and without the risk of a missed step.

A Trivandrum distributor that receives one sales file per branch every month can combine all of them, clean them and feed a PivotTable in seconds once the query is built.

Getting data into Power Query

Quick answer: Go to Data, then Get Data, choose the source, and click Transform Data to open the Power Query Editor.

  • From Table/Range: data already in the workbook.
  • From Text/CSV: exports from billing or bank systems.
  • From Workbook: another Excel file.
  • From Folder: many files with the same layout.
  • From Web or database: tables on web pages, SQL Server and more.

The editor shows a preview, the list of Applied Steps on the right and the formula bar with the M code behind each step.

Cleaning steps beginners use most

Quick answer: Remove unwanted rows and columns, promote headers, set data types, trim text, split columns, and replace errors. Each click becomes a step you can edit or delete.

TaskWhereExample
Use first row as headersHomeBank export with headers in row 1
Remove top or blank rowsHome > Reduce RowsReport titles above the data
Change data typeColumn header iconText dates to real dates
Trim and cleanTransform > FormatExtra spaces in customer names
Split columnHome > Split Column"Kochi-Branch 2" into city and branch
Replace valuesTransform"N/A" to blank
Fill downTransform > FillRegion shown only on the first row of a group

Unpivot: turning a wide report into a proper table

Quick answer: Select the columns that should stay as labels, then choose Unpivot Other Columns. Twelve month columns become two columns: Month and Value.

Many reports arrive with one column per month. They look readable but are hard to analyse. After unpivoting, a PivotTable or chart can slice the data by month, product and region easily. Our PivotTables guide shows what to do next.

Merge and append queries

Quick answer: Append stacks tables with the same columns, one under another. Merge joins tables side by side on a matching column, like XLOOKUP but for whole tables.

  • Append: January, February and March sales into one table.
  • Merge: add the product category from a product list to every sales row, matching on product code.
  • Join kinds: left outer keeps every sales row; inner keeps only matches; anti joins find rows with no match, useful for spotting missing master data.

You can browse every programme we run on the all courses page, or start at the Cokonet Academy homepage.

Combine files from a folder

Quick answer: Put all files with the same layout in one folder, choose Get Data > From Folder, then Combine and Transform. New files dropped into the folder are included on the next refresh.

Power Query builds a sample query from the first file and applies it to all of them. Keep file layouts identical, keep only data files in the folder, and add the file name as a column so you know which branch or month each row came from. Need help automating a report at work? Book a free consultation or call +91 8075 400 500.

Power Query, Power BI and your career

Quick answer: The same Power Query editor runs inside Power BI, so learning it in Excel is a direct step into data analytics. Employers in MIS, accounts and analytics roles value it highly.

MIS executives, accountants and analysts in Kerala use Power Query to cut report preparation from hours to minutes. If you enjoy it, the natural next steps are Power Pivot, DAX and Power BI. See our guides to DAX functions for beginners and the Excel test for job interviews.

Power Query tips that prevent broken refreshes

Quick answer: Rename steps clearly, set data types near the end, avoid hard-coded column lists where files change, and keep source paths in one place.

  • Name steps: "Removed blank rows" is easier to fix later than "Filtered Rows 3".
  • Watch column names: if an export renames a column, steps that reference it fail; ask the source team to keep headers stable.
  • Use a parameter for the folder path: moving files then needs one change, not ten.
  • Load to a table or the Data Model: large data sets work better in the Data Model with Power Pivot.

Test every new query by adding a fresh file and pressing Refresh before you share the report.

Why Cokonet Academy for Advanced Excel training

Cokonet Academy is an NSDC partner training institute with campuses in Trivandrum and Kochi. Find us on Google: Cokonet Academy, Trivandrum and Cokonet Academy, Kochi.

  • NSDC partner training institute with campuses in Trivandrum and Kochi, plus a Bengaluru centre and online-live batches
  • 11,200+ successful placements and 50+ corporate hiring partners
  • Expert trainers in SAP (FICO, MM, ABAP), AWS, Python with AI, Data Analytics and Power BI
  • Placement support until you are hired, with mock interviews and resume reviews

Advanced Excel at Cokonet is taught on real business files: sales registers, ledgers and HR sheets, so PivotTables, Power Query and dashboards are practised on the kind of data your first employer will hand you.

Course details

  • Course: Advanced Excel, Cokonet Academy
  • Duration: 6 to 8 weeks, weekday and weekend live batches
  • Mode: classroom in Trivandrum and Kochi, or online-live; English with Malayalam support
  • Covers: XLOOKUP and dynamic arrays, PivotTables and Power Pivot, Power Query, interactive dashboards, forecasting and VBA macros
  • Next step: the natural first step into the Data Analytics course (SQL, Power BI, Python)
  • Placement: support until you are hired

Modules and batch dates: Advanced Excel course in Kerala.

Visit our training centres

Book a free consultation and sit in on a live session before you decide. We are open Monday to Saturday, 9:30am to 10:00pm.

Enrol now

Learn Power Query, PivotTables, dashboards and macros on real business files. See the course and next batch, visit Cokonet Academy, or call and WhatsApp +91 8075 400 500.

Talk to a Cokonet counsellor - free, honest, and if we're not the fit, we'll say so.

Frequently asked questions

What is Power Query used for?

Importing, cleaning, reshaping and combining data from files, folders and databases, with steps that repeat on refresh.

Is Power Query available in all Excel versions?

It is built into Excel 2016 and later, including Microsoft 365, under Data > Get and Transform Data.

Do I need to learn M code?

No for most tasks; the editor writes M code for you, though small edits become useful later.

What is the difference between merge and append?

Append stacks tables vertically, while merge joins tables side by side on a matching column.

How do I unpivot columns in Power Query?

Select the label columns, right-click and choose Unpivot Other Columns.

Can Power Query combine multiple Excel files?

Yes, use Get Data > From Folder and Combine to merge all files with the same layout.

Does Power Query change the original data?

No, it reads the source and loads a cleaned copy, leaving the original file unchanged.

Is Power Query the same in Power BI?

Yes, Power BI uses the same Power Query editor and M language.

Is Power Query asked in Excel interviews?

Yes, MIS and analyst roles often ask about cleaning data, unpivoting and combining files.

Where can I learn Power Query in Kerala?

Cokonet Academy's Advanced Excel course in Trivandrum, Kochi and online-live covers Power Query on real business data.

Keep reading