Power Query Editor in Power BI – Your Go‑to Guide for Shaping Data

When you first fire up Power BI Desktop, the first thing you’ll probably do is click Transform Data on the Home ribbon. That little button opens the Power Query Editor – the workspace where raw data gets a makeover before it ever reaches a visual.

Inside the editor you’ll see a clean pane on the left (the Queries pane) and a preview of your table on the right. To pull data in, hit New Source and pick anything from an Excel workbook to a SQL Server database or even a web‑based CSV. In our demo we grabbed an Excel file called My movie list.xlsx; once loaded, the rows and columns pop up ready for tinkering.

Renaming is the first step of housekeeping. You can rename the query itself (right‑click the entry in the Queries pane and choose Rename) – we changed “Sheet1” to “Movie Data” to keep things clear. Columns are just as easy: right‑click the column header, pick Rename, and type the new name. So the column called TITLE became MOVIE NAME. A little extra effort now saves a lot of confusion later.

Often the first row of a spreadsheet is actually the header row, but Power Query might treat it as data. No problem – go to the Transform tab and click Use First Row as Headers. If you ever need to reverse that, the Use Headers as First Row option is there too.

Next up, data types. A column that looks like numbers might still be stored as text, which throws off calculations. Right‑click the column, choose Change Type, and pick the correct type – Whole Number, Decimal Number, Text, Date, etc. In our example we switched the RANK column from a whole number to a decimal number to allow fractional scores.

Cleaning up text is surprisingly satisfying. Under the Transform tab you’ll find a Format dropdown: turn text to uppercase, lowercase or proper case; add a prefix or suffix; trim leading/trailing spaces; or even strip out non‑printable characters with the Clean command. We ran a quick “Uppercase” on the genre column to make everything look uniform.

Sometimes you just need to get rid of junk. The Remove Rows button on the Home tab lets you drop the top, bottom, alternate, duplicate, blank or error rows. If you only want to keep a specific slice, use Keep Rows instead. Likewise, Remove Columns deletes columns you don’t need – we tossed out the RATING column because it was redundant.

What if two columns belong together? The Merge Columns tool (under Transform) lets you select multiple columns, choose a separator like a space or comma, and give the new column a name. We merged GENRE and RATING into a single field called Merged – handy for quick concatenations.

Finally, replacing values is a quick fix for typos or missing data. Click Replace Values, type the value you want to change (e.g., null) and the replacement (e.g., “Unknown”). After that, the GENRE column showed “Unknown” wherever the original entry was blank.

All these steps happen in the Query Editor, but remember – nothing is permanent until you click Close & Apply. That button pushes the cleaned, transformed data back into Power BI’s data model, where you can start building charts, slicers, and dashboards with confidence.