Power Query is the Microsoft Data Connectivity and Data Preparation technology that allows the users to flawlessly access data stored in hundreds of data sources and reshape it to fit their needs.
What is Power Query Editor in Power BI?
Power Query can replace, merge, clean and transform all the messy data and display it in a user-friendly way. The important thing is that the next time you receive the same messy file, all you need to do is press the Refresh button and your data will automatically be cleaned up and displayed the correct way.
Here, there is no need of any formulas, text to columns, trim spaces, VLOOKUP, find & replace etc. like that in Excel.
Apart from cleaning data, Power Query also append or merge two separate tables together as well as split the columns and even create extra columns in your data which can display your custom calculations.
Power Query also executes transformations that are very complex in VBA or SQL.
Note: First, you need to have Power BI desktop installed on your PC.
Steps to connect to data in Power Query
⇒ STEP 1: Get the data from different kind of data sources
To connect to the data sources in Power BI Desktop, go to
Home > Get Data > Excel
Note: You can choose any kind of data file here.
⇒ STEP 2: Select the data file from your PC or any database to load in Power Query
Select any file you want from your computer after clicking on the file type. This will let you select the number of sheets from the chosen file. You can also see the preview of your data as shown in below image. To load the data in Power Query, just click on the load button.
⇒ STEP 3: Open the Power Query Editor Window
To open the Power Query Editor in Power BI, perform the below steps:
Go to Home > Edit Queries Button > Edit Queries option
You are now inside the Power Query Window.
Read More :
- Keep Duplicate Records
- Split Column By Number Of Characters
- Duplicate Columns
- Use First Row As Headers