Power Query is part of Microsoft Excel. It’s a powerful tool that can help you with repetitive tasks. If you are working with data and finding that you are writing complicated formulas to “clean” or format text strings, for example, you might find that Power Query is much easier. Power Query is also known as Get and Transform. The Power Query features are under the Data tab, on the left, in the Get and Transform group. Data Analysis projects almost always involve some level of transformation of the data because it is almost always never perfectly “clean”. You may find you need to extract, transform and load (ETL). It’s just best practice to assume you need to clean the data before analyzing it.

Power Query comes with a very powerful transformation engine. In this series of posts, we’ll look at examples of using those transformations.

A Few Power Query Tips

• Power Query will want your data to be a table or a named range before it imports the data, so why not convert it to a table (Ctrl+T) beforehand? Ranges must be named. The downside to named ranges is that when you add rows of data to ranges, they don’t automatically expand, whereas tables do.
• Always name your tables to a descriptive name before importing them to Power Query (the default name is Table1, Table2, and so on).
• Change your table style (color, alternate rows highlighted etc) as you wish. To use a default style, take an Empty Excel file, right-click the style, set it as the default, and save the file as an Excel template and then use that template.
• After bringing the data into the Power Query Editor, check and change (if necessary) the name.
• The next thing to do is to check the data types and change them if necessary
• A word of warning. In Power Query if you change a columns with decimal numbers from Decimal Number to Whole Number, you change the underlying data itself, not just the way it looks.
• To test a column transformation you are unsure of, you can first select it, right-click and choose Duplicate and work on that duplicate.
• To do the same transformation on multiple columns at once, cherry-pick them by using the Control key.