Load database subset into Excel with Power Query

Excel has a "Get Data" function, or Power Query, that enables you to seamlessly import data from various sources such as databases, files, online services, and more. It offers a user-friendly interface to transform and load data into Excel for analysis and reporting. For example, our World Cities Database contains over 4 million rows, but Excel can only load 1 million rows at once. So, we can use Power Query to load and view a subset of the database, such as the data for a few countries. Here's how:

1. Open Excel and create a new workbook or open an existing one.

2. Go to the "Data" tab on the Excel ribbon and in the "Get & Transform Data" group, click on the "Get Data" dropdown arrow. You can find Power Query if you have a Microsoft 365 subscription or a standalone copy of Excel 2016 or later.

3. Choose the data source you want to connect to, such as "From File," "From Database," "From Web," etc. In this instance, we're utilizing the text/csv "World Cities Database" file. If you're a customer, you'll be emailed instructions on how to download this file. If not, you can follow along by dowloading our free world cities database.

4. Follow the prompts to provide necessary connection details. For example, if you're importing data from a CSV file, navigate to the file's location and select it.

5. Once the data is loaded, click "Transform Data" to open the data in the query editor.

6. Once you've connected to your data source, the Power Query Editor will open. Here, you can perform various transformations on your data. Use the Power Query Editor's interface to filter, sort, merge, pivot, and more. These transformations help you clean and reshape your data before loading it into Excel. For this tutorial, we are going to focus on loading specific countries. Click the down arrow in the "Country" column and choose "load more.

7. Once the list of countries is displayed, choose only the countries you wish to load into Excel.

8. After performing necessary transformations, click "Close & Load" within the Power Query Editor. Specify the destination for loading the data: a new worksheet or an existing one. Finally, confirm your choice by clicking "OK," and the data will be loaded into Excel.

Now that you've successfully transformed and loaded your data using Power Query, it's important to recognize that this tool offers an expansive range of capabilities beyond what we've covered in this tutorial. Don't hesitate to explore further and experiment with different transformations. As you continue your data-driven journey, remember to explore all our available databases at https://simplemaps.com/data.

If you encounter any difficulties or have questions, please don't hesitate to reach out to us at [email protected].