Excel: Power Query

Problem: I have a table showing revenue by US state. My manager wants me to calculate Sales Per Capita. Where do I get population by US state?

Strategy: Use Power Query to search for the data online and load to Excel.

1. On the Power Query tab, choose Online Search.

1. Power Query can load data from many sources.
   2. Search for "œpopulation by state".
   3. Power Query provides a list of search results.
   4. Hover over any item in the search results to see a preview of the columns in the data.

   

2. Power Query finds data online that can be imported.
   5. When you find a data source, click Edit in the preview window. If you click Load, the data is loaded to Excel without any data cleaning. Edit allows you to record the cleansing steps in the "œM" language.

   

3. Choose to edit before loading into Excel.

   You have a wide variety of choices in the Query Editor. Explore options on the Home, Transform, and Add Column tabs. Also, many popular choices are available in the Right-Click menu.

   6. Right-click the Rank_0 column. Choose Remove Column.

   

4. Remove the columns that are not needed.

   Tip: If you have to remove many columns, select the first column, then Ctrl+Click other columns. Use the Remove Columns icon in the Home tab to remove all selected columns.

   7. Some data contains unusual rows or totals that you do not need. Open the Filter dropdown for a column and uncheck those rows.

   

5. Use the filter dropdowns to remove rows.

   As you continue cleaning the data, your past steps are shown in the window on the right side of the screen. You can click any item in the list and delete it.

   

6. All of the steps you've done are shown.

   If you want to dig deeper into the "œM" code, choose View, Advanced Editor and you can see and edit the code. For more details on this language, read "œM is for (Data) Monkey" by Ken Puls and Miguel Escobar.

   8. If you want to force a column to be numeric or date, select the column heading and apply a Data Type using the dropdown in the Transform group of the Home tab.
   9. Right-click a column and choose Rename to give it a meaningful heading in Excel.
   10. When you are done editing the query, choose Home, Close & Load. The data is loaded to your Excel workbook.

   This is great that you have the data in Excel. But it is not a static list of data. Power Query remembers where the data came from, what steps you used to clean it. When you click the Refresh button, Excel will go back to the data source, load the new data, and re-apply all of your cleaning steps.

   Additional Details: With one cell in the results selected, click the Edit button to re-edit your query.