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.
 
Power Query in Power BI
 
Power Query in Power BI 
 
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
 
Power Query in Power BI 
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
 
Power Query in Power BI
 
Transform
 
Power Query in Power BI
 
Add Column
 
Power Query in Power BI
 
View
 
Power Query in Power BI
 
Tools
 
Power Query in Power BI
 
Help
 
Power Query in Power BI
 
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.
 
Power Query in Power BI
 
Power Query in Power BI
 
Once you click on ‘Open’ your file will be ready to import. Do not click on load this time, click on ‘Transform Data’
 
Power Query in Power BI
 
After click on ‘Transform Data’, Power query editor will be open.
 
Power Query in Power BI
 
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.
 
Power Query in Power BI
 
Once you click on duplicate you can see the duplicate table just below this table. As shown in the below image.
 
Power Query in Power BI
 
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’.
 
Power Query in Power BI
 
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!