Power Query In Power BI

Introduction

 

Power Query is the Data Connectivity and Data Preparation innovation that empowers end clients to flawlessly import and reshape information from inside a wide scope of Microsoft items, including Excel, Power BI, Analysis Services, Common Data Services for Apps and Analytics, and that's only the tip of the iceberg.

 

The Power Query Editor is the essential data arrangement experience, permitting clients to apply more than 300 distinct information changes by seeing information and choosing changes in the client experience. These information change capacities are regular overall information sources, paying little heed to the basic information source confinements

 

Use of Power Query

• Supportmultipledata sources (databases, documents, website pages, internet-based life, APIs, distributed storage and so on.)
• Collect and combine data (merge, join, etc.) from different spots
• Add and remove columns of data
• Add and remove data row
• Reshape data (transposing, pivoting, un-pivoting and other innovative ways)
• Compose equations to manipulation of information
• Publish updated datasets

Power Query is a powerful feature of Microsoft. It is a part of Power BI. To getPower Query, Just click on “Transform Data” button on the home page and you enter the Power Query world.

 

 

 

 

This is a way to open a power query editor. You can get data from power query editor by click on ‘New Source’ select source like Text/CSV

 

 

The ribbon in Power Query Editor contains six tabs—Home, Transform, Add Column, View, Tools, and Help. Below images show all features of these tabs

 

Home

 

 

Transform

 

 

Add Column

 

 

View

 

 

Tools

 

 

Help

 

 

You can also open the power query editor after connecting the data source. For example:

 

If you have CSV file, to import the CSV file (see the article Import Data Into Power BI From CSV) you click on load but if you will click on ‘Transform Data’, power query editor will be opened and you will be able to transform the data like add column, remove row etc.

 

Similarly, you can take any other source and transform the data.

 

You can perform more complex data transformations such as changing the data type, deleting columns and adding calculated fields.

 

Creating Duplicate table in Power Query Editor

 

Here I am going to show you how to create duplicate tables in the power query editor. To do so, first, you need to get data from any source. I am using a CSV file.

 

 

 

Once you click on ‘Open’ your file will be ready to import. Do not click on load this time, click on ‘Transform Data’

 

 

After click on ‘Transform Data’, Power query editor will be open.

 

 

Here you can see, your table is appearing in the power query editor. To create its duplicate, select this table and go to home page. Click on ‘Manage’ (Drop down menu). Here you can see three options ‘Delete’, ‘Duplicate’, and ‘Reference’.

 

Click on duplicate.

 

 

Once you click on duplicate you can see the duplicate table just below this table. As shown in the below image.

 

 

To delete the duplicate table you can delete it from Manage drop-down or simply right click on this table and delete it.

 

Sort Data

 

You can sort the data by selecting any column and go to the Home page and click on ‘Sort’. Here you can get ascending or descending sorting. I selected ascending sorting by ‘BusinessEntityId’.

 

 

To save the changes you have done just go to ‘File’ and click on ‘Close and Apply’.

 

Summary

 

Hope you understand the basics of the Power query editor. Stay with us to learn more about Power Query Editor. Thanks for reading!