Independence Day Offer - FLAT 30% OFF | JTINFLAT30

- SQL Server Blogs -

Power BI - Getting Started with Query Editor in Power BI



Introduction

We all know that Power BI is a popular BI reporting tool. It has very strong capabilities of generating power full BI reports from any data source, be it a standard relational database management system like SQL Server or Oracle or flat files like text or CSV file. In one of our earlier blogs, we have learned about different components of a Power BI desktop application. In this blog, we will learn how to use Power BI to develop a simple Power BI report.

The most important component of a Power BI tool is the Query Editor. Next, we will learn about Power BI Query Editor.

Power BI Query Editor

With Power Query Editor, we can explore, characterize, and perform data change activities over an information source. To show the Query Editor Dialog box, we need to connect with an information source, and click Edit Query in the Navigator pane or double click a query in the Workbook Queries pane. Next, we will see a step by step run through on how to use data editor. We will be using the Adventureworks2016 database for all the examples in this blog.

Using Power BI query Editor

Step 1

Open Power BI Desktop application.

Power Bi query editor

Step 2

Click on Get Data and select SQL Server.

Power Bi query editor

Step 3

The following screen appears. Provide the Server name and select direct query and click on Ok.

Power Bi query editor

Step 4

In the Navigator, screen selects the necessary tables and database from the database list and click on Load.

Power Bi query editor

Step 5

Click on edit queries.

Power Bi query editor

Step 6

The following screen appears.

Power Bi query editor

Step 7

Click on the advance editor.

Power Bi query editor

The query editor window will open like below.

Power Bi query editor

There is another way how we can call the query editor. For the next few lines, we are going to discuss that.

Step 1

Open the Power BI desktop application.

Open the Power BI desktop application.

Step 2

Click on edit queries to open the query editor window.

Open the Power BI desktop application.

Step 3

The query editor window appears.

Open the Power BI desktop application.

Step 4

Click on the new source and select SQL Server.

Open the Power BI desktop application.4

Click on connect.

Step 5

We put the server name, the database name and also write the query that would be used for generating the Power BI report.

Open the Power BI desktop application.5

Once done we will click on Ok.

Step 6

When you click on Ok, the application will give the output of the query in a pop up window.

Open the Power BI desktop application.6

Then we click on OK.

Step 7

You will see the query is added to the report.

Open the Power BI desktop application.7

Step 8

When you exit the power query editor it asks whether you want to apply the changes. Click Yes.

Open the Power BI desktop application.8

Step 9

The changes will be applied into the report as below.

Open the Power BI desktop application.9

Renaming Query in Query Editor

Once the query is created in Query editor we might need to rename the query. Next, we will discuss how to do it.

Step 1

Open a Power BI report and click on Edit Queries.

Renaming Query in Query Editor1

Step 2

The query editor window pops up. Right-click on the query you want to rename and select rename.

Renaming Query in Query Editor2

Step 3

Provide the new name of the query and click Close and Apply.

Renaming Query in Query Editor3

The query will be renamed and the changes will be applied to the report.

How to combine two files using Power BI Query Editor

We create two excel files from the output of the following two queries from the Adventureworks database.

select * from [Sales].[SalesOrderHeader]

 select * from [Sales].[SalesOrderDetail]

Now let us combine the two files using Power BI query editor.

Step 1

Open the Power BI desktop application.

How to combine two files using Power BI Query Editor1

Step 2

Double click on the GetData option and select the folder option.

How to combine two files using Power BI Query Editor2

Click the Connect button.

Step 3

The system will ask to select the folder in which the files are located.

How to combine two files using Power BI Query Editor3

Select the folder and click on the Ok button.

Step 4

A popup window appears showing details of all the files located inside the folder. Select the related file and click on the Combine button.

How to combine two files using Power BI Query Editor4

Step 5

Click on apply changes to apply the change in the report.

How to combine two files using Power BI Query Editor6

Step 6

The final output looks like below.

How to combine two files using Power BI Query Editor6

Relationships

Once you have generated we can set relationships between the tables. Power BI query editor allows us to set the relationship between tables. Next, we will learn how to do it.

Step 1

Click on Manage Relationships on the main Power BI window.

Relationships1

Step 2

The following window pops up.

Relationships2

Click on the New button.

Step 3

The following screen pops up.

Relationships3

Click the Ok button to create the relationship.

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Filtering Rows in Power BI Editor

Next, we will learn how to filter rows of a query table from within Power BI query editor.

Step 1

Click on Edit Queries to go into Power BI query editor.

Filtering Rows in Power BI Editor1

Step 2

The Power BI Query Editor appears.

Filtering Rows in Power BI Editor2

It shows all the details of the queries used in the report.

Step 3

There are two buttons in the Power BI Query Editor window –Keep Rows and Remove Rows.

 Filtering Rows in Power BI Editor3

Keep Rows

Keep Top Rows: This shows a specified number of rows from the top of the dataset.

Step 1

Click on the Keep Top Rows button.

Keep Rows1

Step 2

A window pops up asking for the number of rows to be displayed from the top.

Keep Rows2

Step 3

The final output is as follows.

Keep Rows3

Keep Bottom Rows: This shows a specified number of rows from the bottom of the dataset.

Step 1

Click on the Keep Bottom Rows button.

Keep Bottom Row1

Step 2

A window pops up asking for the number of rows to be displayed from the bottom.

Keep Bottom Row2

Step 3

The final output is as follows.

Keep Bottom Row3

Keep Range of Rows: Displays a range of rows provided by the user.

Step 1

Click on Keep Range of Rows button.

Keep Range of Rows1

Step 2

A window pops up asking the user to provide the range of rows to be displayed.

Keep Range of Rows2

Click Ok.

Step 3

The final output displays the range of rows provided as a parameter.

Keep Range of Rows3

Remove Rows

Remove Top Rows: This removes a specific number of rows from the top.

Step 1

Click on Keep Remove Top Rows button.

Remove Rows1

Step 2

A window pops up asking for the number of rows to be removed from the top.

Remove Rows2

Step 3

The final output is like below

Remove Rows3

Remove Top Rows: This removes a specific number of rows from the bottom.

Step 1

Click on Keep Remove Bottom Rows button.

Remove Top Rows1

Step 2

A window pops up asking for the number of rows to be removed from the bottom.

Remove Top Rows3

Step 3

The final output looks like below.

Remove Top Rows3

Remove Alternate Rows: It removes every alternate row from the first and last mentioned rows of the dataset.

Step 1

Click on Remove Alternate Rows button.

Remove Alternate Rows1

Step 2

The remove alternate rows window pops up. It asks you to enter the row from where the elimination will begin, a number of rows to delete and the number of rows to keep.

Remove Alternate Rows2

Step 3

The final output looks like below.

Remove Alternate Rows3

Eliminating columns of a dataset using Power BI query editor

Step 1

Select the column you want to remove and click on the Remove column button in Power BI Query Editor and select Remove Columns option.

Eliminating columns of a dataset using Power BI query editor1

The column will be removed from the dataset.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

Add columns to a dataset using Power BI query editor

Step 1

Click on Choose Columns and select Choose Columns option.

Add columns to a dataset using Power BI query editor1

Step 2

The choose columns popup window comes up.

Add columns to a dataset using Power BI query editor2

Select the number of columns you want to display in the dataset and click Ok button.

SQL Server Training & Certification

  • Detailed Coverage
  • Best-in-class Content
  • Prepared by Industry leaders
  • Latest Technology Covered

Lookup in Power BI Query Editor

Lookup is mainly used in DAX queries. DAX queries are a special kind of query format that is used in Power BI. This is beyond the scope of this blog. But we can certainly share the syntax of the LOOKUPVALUE command below for readers to explore further.

LOOKUPVALUE( , , [, , ]…[, ])

Merge Queries in Power BI Query Editor

Step 1

Go to the Power BI query editor and click on the Merge Queries button.

Merge Queries in Power BI Query Editor1

Step 2

The following screen appears

Merge Queries in Power BI Query Editor2

It requests you about the table you want to merge together and what will be the join type. For the following example, we will be merging the SalesOrderHeader table with Customer and the join kind will be Left Outer. Once you are done click on Ok. We also need to select the same number of columns from both the tables having the same data types.

Step 3

The final output looks like below.

Merge Queries in Power BI Query Editor3

Create a new table using Power BI Query Editor

Step 1

Open Power BI Query Editor. Click on Enter Data button.

Create a new table using Power BI Query Editor1

Step 2

The following window pops up.

Create a new table using Power BI Query Editor2

By default, it comes with one Colum. But you can add multiple by right-clicking on the column and click on Insert option.

Step 3

Click on Ok and the new tables will be added in the list.

Create a new table using Power BI Query Editor3

You can rename the table name to your choice.

Power BI for Data Scientist

As data becomes more accessible, manipulating vast amounts of available data to drive insights and make business decisions can be a challenge. Business leaders at every level need to become data literate and be able to understand data and analytical concepts that may have previously seemed out of reach, including statistical methods, machine learning, and data manipulation. With this spread of data literacy comes the powerful ability to make educated business decisions that rely on the smart use of data, rather than on an individual’s opinions. In the past, these tasks were extremely complex and would be handed off to engineers. With the tools that exist today, business leaders are able to dive into their own analytics and uncover powerful insights.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

Summary

In this blog, we tried to touch upon different tools that are used to create different BI Tools. These are only a short discussion of all the tools. For a detailed study of this tool, proper training using a specialized course on Power BI application is required.


    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

1 day 13 Aug 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

8 days 20 Aug 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 17 Aug 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

3 days 15 Aug 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

2 days 14 Aug 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

5 days 17 Aug 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

2 days 14 Aug 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

2 days 14 Aug 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

5 days 17 Aug 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

1 day 13 Aug 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

4 days 16 Aug 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

6 days 18 Aug 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews