Hello and welcome to the first part of our series on Power Query in Excel.

What is Power Query?
Power Query (aka "Get & Transform") is a powerful data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources. It's an Excel add-in that provides an intuitive user interface for data discovery, data transformation, and enrichment.

Why Use Power Query?
With Power Query, you can:
- Import Data: Power Query allows you to import data from a wide variety of sources (e.g. excel workbook, csv files, text files, JSON, XML etc.).
- Clean and Transform Data: You can easily clean and transform your data into a format that you can use within Excel. This includes tasks like removing duplicates, changing data types, and merging data from multiple sources.
- Automate Data Updates: Once you have your data in the desired format, Power Query can refresh your data on command, saving you the time of manually updating your data.


Importing Data

To import data into Power Query: 

1. Go to the Data tab in Excel. 

2. Click on Get Data. 

3. Choose the type of data source you want to import from. 

Power Query supports a wide variety of data sources, from Excel workbooks and CSV files, to SQL Server databases and online services like SharePoint.



Loading Option

You have two options to load that data back into Excel:

  1. Load: This option loads the data directly into a new worksheet in your Excel workbook. It’s a quick and easy way to bring your data into Excel, but it has a limitation: it can only load data up to the row limit of Excel, which is 1,048,576 rows.
  2. Load To: This option gives you more flexibility. It allows you to load the data into different places, not just a new worksheet. You can load the data into the Data Model, a PivotTable, a PivotChart, or even just create a connection without loading the data into the workbook.

 

Load to Options

When you load data from Power Query, you have several options:

  1. Table: This option loads the data into a new Excel table in your workbook. This is useful when you want to work with the data directly within Excel, using Excel’s built-in features like sorting, filtering, and formulas.
  2. PivotTable Report: This option loads the data into a new PivotTable. PivotTables are powerful tools that allow you to analyze your data in various ways by dragging and dropping fields into different areas of the PivotTable. You can summarize your data by categories and subcategories and, and create calculated fields.
  3. PivotChart: This option loads the data into a new PivotChart, which is essentially a graphical representation of a PivotTable. PivotCharts are dynamic and interactive, allowing you to visualize your data in a way that can be easier to understand and interpret.
  4. Only Create Connection: This option creates a connection to your data source but doesn’t load the data into your workbook. This is useful when you have a large dataset that you don’t need to work with directly, or when you want to use the data in multiple PivotTables or PivotCharts without storing the data in your workbook multiple times.
  5. Add this data to the Data Model: This option loads the data into Excel’s Data Model. The Data Model is a powerful feature that allows you to work with data from multiple tables, much like a relational database. It also allows you to work with more than 1 million rows of data, overcoming Excel’s row limit. When you load data into the Data Model, it uses a highly compressed storage mechanism, which makes it more memory-efficient than loading data directly into a worksheet.

Why Load To is Better

The “Load To” option is generally more powerful and flexible for several reasons:

  • Data Model: The “Load To” option allows you to load data into Excel’s Data Model. This is a powerful feature that lets you work with data from multiple tables, much like a relational database. It also allows you to work with more than 1 million rows of data, overcoming Excel’s row limit.
  • Memory Efficiency: When you load data to the Data Model, it uses a highly compressed storage mechanism, which makes it more memory-efficient than loading data directly into a worksheet.
  • Data Analysis: By loading data into the Data Model, you can use it in PivotTables, PivotCharts, and Power View reports. You can also define relationships between multiple tables and create powerful data analysis solutions.
  • Connection Only: If you don’t need to work with the data immediately, you can choose to create a connection only. This means the data won’t be loaded into your workbook, saving memory. You can then load the data whenever you need it.


Power Query Editor

The Power Query Editor is a tool within Power Query that allows you to perform detailed transformations on your data. It provides a user-friendly interface where you can apply various operations to clean, transform, and shape your data to meet your needs. In the Power Query Editor, each step you take to transform the data is recorded. This allows you to automate the data preparation process, as you can reapply the same steps to new data in the future.



That concludes our introduction to Power Query in Excel. We have explored the graphical user interface and touched a bit on its capabilities. In Part 2, we’ll dive deeper into the practical aspects of Power Query, focusing on basic data transformations. Stay tuned for more insights and hands-on learning.


To learn more, visit What is Power Query? - Power Query | Microsoft Learn