Sign in or start a free trial to avail of this feature.
1. Introduction to the Query Editor
In this lesson, we will introduce the Query Editor. We’ll learn what a query is, what the Query Editor does and understand why it is useful to the data analyst.
What is a Dashboard?
- An easy-to-read, single-screen interface showing the Key Performance Indicators (or KPIs) for a business unit
Uses of Dashboards
- Provide a high-level overview of the business
- Spot trends and obtain insights from raw data
- Improve decision making and results
- Data analysts often spend much of their time extracting and transforming datasets before actually analyzing them
- The Query Editor is an environment for transforming datasets before importing them into Power BI Desktop
What is a Query?
- A query is a copy of the data that is to be imported
- The data source is stored with the Power BI Desktop file and is separate from the source data
- Editing the query modifies the data without altering the original source data
Benefits of the Query Editor
- It is a dedicated environment for transforming and modifying data
- We can work with the query, instead of the underlying data source
- The query can be easily refreshed, modified or repeated
It's often said that a data analyst spends most of their time importing data, cleaning it, transforming it, etc rather than actually analyzing it. If you've imported data from complex sources, such as databases or web pages, you may have spent long periods of time transforming that data into a useful format before doing any analysis work. Power BI Desktop provides the Query Editor to make this process much quicker and easier. In this lesson, we'll learn about the concept of queries and how they're used in Power BI. In our previous courses on Power BI Desktop, we focused on analyzing data sets by creating visualizations and reports. We did not modify the data sets as part of these lessons, but rather imported data into Power BI Desktop and analyzed it as is.
This is not necessarily reflective of the real world. Often, your data will not come formatted in a way you like.
Furthermore, your data may be spread across multiple files or sources, which you'll have to combine. The Query Editor is a feature that allows you to transform larger complex data sets before you load them into Power BI. If you have experience transforming data in Excel, you may have used a feature known as Power Query or Get and Transform. The Query Editor in Power BI is largely the same as this feature. We'll see how to use the Query Editor to import several data sets, combine them into a single query, and transform the data into a format that can be easily analyzed in Power BI. What is a query? Anytime we import data into Power BI Desktop, we create a query, a copy of the data being imported.
It's separate from the source data and is stored as part of the Power BI Desktop file. By using the Query Editor, we can transform the data set without affecting the underlying data itself. Often, complex data sets are not ideally suited to data analysis. They may contain more variables than you need or may be poorly formatted. The Query Editor lets you convert your data to a more appropriate format. There are several advantages to using the Query Editor. First, all the data transformation takes place in a dedicated, easy-to-use environment instead of directly at the source. You often won't have permission to modify the source data for complex data sources, like databases. The Query Editor lets you transform these data sets without affecting the database itself. Furthermore, the query is easily modified or even repeated. If the source data updates, you can simply refresh your data set in Power BI Desktop. This will run the query again, automatically applying any changes to the updated data set as well as visualizations and reports. There are two ways to access the Query Editor in Power BI Desktop. If you've already imported a data set, you can select edit queries from the home tab, which will display the query that was created for your data set. Alternatively, you can access the Query Editor at the point of importing the data set. We'll take a look at this process in the next lesson.