Platform Banner

Power BI - Introduction to the Query Editor Tutorial

In this video, you will learn about the intelligent search feature in Microsoft 365. The video covers the search tools in Microsoft 365, including searching on your computer, using Microsoft Search, finding content and people, searching your emails, and more.
This will help you save time and easily find documents, information, and people within the Microsoft 365 suite.
Gain efficiency in your searches with Microsoft's intelligent search tool!

2:33
3823 views

Available in Microsoft365

Objectifs :

This document aims to provide a comprehensive overview of the Query Editor in Power BI Desktop, detailing its functionalities, user interface, and practical applications for data transformation and management.

Chapitres :

Introduction to Query Editor
Power BI Desktop includes a powerful tool known as Query Editor, which is essential for formatting and transforming imported data. Often, data is not presented in the desired format upon import, and Query Editor addresses this issue by allowing users to create queries, change names, transform data, create new columns, and apply various edits.
Accessing Query Editor
Query Editor opens in a separate window when you select 'Edit' during the data import process. Alternatively, you can launch it at any time by clicking the 'Edit Queries' button located under the Home tab.
User Interface Overview
Once Query Editor is loaded with data, several sections will appear: - **Ribbon**: This section allows interaction with the query data, enabling modifications and extraction of elements. - **Queries Pane**: Located on the left, this pane lists all available tables for selection, display, and formatting. In Power BI, a table is referred to as a query. - **Data Pane**: The middle pane displays the data from the selected query, which is available for editing. - **Query Settings Pane**: This pane lists the properties and steps applied during the transformation process. Each operation performed in Query Editor is recorded in the 'Applied Steps' list.
Managing Applied Steps
The 'Applied Steps' list in the Query Settings pane allows users to: - Cancel or delete specific edits. - Review the name of a step. - Perform quick actions by right-clicking on a column, which provides options for transformations, copying a column under a new name, and replacing values. - Split text columns using common delimiters.
Saving Transformations
To save your transformations, select 'Close & Apply' under the Home tab. This action will apply all changes made in Query Editor to Power BI Desktop.
Advanced Data Editing
For experienced users, Query Editor offers the 'Append Queries' tool, which allows the addition of data from a new table to an existing query. This feature also enables users to write query expressions for more complex data manipulation.
Conclusion
Query Editor is a vital component of Power BI Desktop that enhances data management capabilities. By understanding its functionalities and user interface, users can effectively transform and manipulate data to meet their analytical needs.

FAQ :

What is Query Editor in Power BI?

Query Editor is a tool in Power BI Desktop that allows users to create and modify queries, transform data, and prepare it for analysis before loading it into the Power BI model.

How do I access Query Editor?

You can access Query Editor by selecting 'Edit Queries' during data import or by clicking the 'Edit Queries' button under the Home tab at any time.

What can I do with the Applied Steps list?

The Applied Steps list allows you to review all transformations made in Query Editor. You can cancel, delete, or modify specific steps as needed.

Can I split text columns in Query Editor?

Yes, you can split text columns using common delimiters by right-clicking on the column and selecting the appropriate transformation option.

What happens when I select 'Close & Apply'?

When you select 'Close & Apply', Query Editor will apply all transformations made to the data and load it into Power BI Desktop for further analysis.

Quelques cas d'usages :

Data Transformation for Reporting

Using Query Editor to clean and transform raw data from various sources into a structured format suitable for reporting in Power BI. This includes renaming columns, changing data types, and removing unnecessary rows.

Combining Data from Multiple Sources

Utilizing the Append Queries feature to combine data from different tables into a single query, allowing for comprehensive analysis and reporting across multiple datasets.

Creating Custom Calculated Columns

Employing Query Editor to create new calculated columns based on existing data, which can enhance the insights derived from the dataset and improve decision-making.

Automating Data Refresh

Setting up queries in Query Editor to automate the data refresh process, ensuring that the reports in Power BI always reflect the most current data without manual intervention.

Glossaire :

Power BI Desktop

A business analytics tool by Microsoft that enables users to visualize data and share insights across their organization or embed them in an app or website.

Query Editor

A powerful tool within Power BI Desktop that allows users to create queries, transform data, change names, create new columns, and apply edits to the data before loading it into the model.

Queries

In Power BI, a query refers to a table that is created to retrieve and manipulate data from various sources.

Ribbon

The toolbar in Power BI that provides access to various commands and features for interacting with the query data.

Query Settings pane

A section in Query Editor that displays the properties and steps applied during the data transformation process.

Applied Steps

A list in the Query Settings pane that shows all operations performed in Query Editor, allowing users to review, cancel, or delete specific edits.

Append Queries

A feature in Power BI that allows users to add data from a new table to an existing query.

Delimiters

Characters used to separate values in a text column, such as commas or spaces, which can be used to split text columns in Query Editor.

00:00:06

or set up as you would like it to be.

00:00:09

To help you format and transform your data,

00:00:12

Power BI Desktop includes a powerful tool called Query Editor.

00:00:16

Query Editor makes it possible to create queries,

00:00:19

change names, transform data types, extract data,

00:00:23

create new columns and apply edits.

00:00:26

Query Editor opens in a separate window with the tables

00:00:29

that you chose when you selected Edit during the data import.

00:00:34

Otherwise you can launch it at anytime using

00:00:36

the Edit Queries button under the Home tab.

00:00:40

Once Query Editor has been loaded with data, several sections will appear.

00:00:45

The ribbon, which makes it possible to interact with the query data,

00:00:49

for example, by modifying the column data type,

00:00:52

extracting elements and replacing values.

00:00:56

The left pane called Queries lists all the tables available for selecting,

00:01:00

displaying and formatting.

00:01:02

A Power BI table is called a query.

00:01:06

In the middle pane, the data from the selected request

00:01:09

is displayed and available for editing.

00:01:12

The Query Settings pane of a request lists the properties

00:01:15

and the steps applied during the transformation procedure.

00:01:19

Each operation performed in Query Editor is displayed in

00:01:22

the Applied Steps list in the Query Settings pane.

00:01:26

You can use this list to cancel, delete or review specific edits

00:01:31

or even change the name of a step.

00:01:34

You can also right click to perform quick actions,

00:01:37

for example, right clicking on a column makes it possible

00:01:40

to display a series of available transformations

00:01:43

such as deleting a table column, copying a column under a new name

00:01:47

and replacing values.

00:01:49

In this menu, you can also split text columns using common delimiters.

00:01:56

To save your transformations, select Close Apply under the Home tab.

00:02:01

Once you have selected Close Apply,

00:02:03

Query Editor will apply the query edits that you made

00:02:06

and will then apply them to Power BI Desktop.

00:02:10

Finally, don't hesitate to explore the features made available

00:02:13

to you under the various tabs to achieve the desired data editing,

00:02:17

for example, you can use the Append Queries tool to add data

00:02:21

from a new table to an existing query

00:02:24

or the Add Custom Column tool that allows experienced users

00:02:28

to write query expressions using the powerful M language.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

14905 Views

Microsoft Power BI Video Training – Discovery

Dashboard, indicators (KPIs), activity tracking are part of your daily vocabulary? The Power BI business intelligence tool is your perfect c...

ALL COURSES