Blog

You are currently viewing Steps to access Power Query in Excel and Power BI

Steps to access Power Query in Excel and Power BI

Loading

In this blog, we will know about the steps to access Power Query using Excel and Power BI Desktop. All the steps were given below.

Steps to access Power Query in Excel and Power BI

First, we are starting to access Power Query in Excel

Step1: Open your Microsoft Excel on your Device.

Note:

  • If you are using Microsoft 365, Office 2016 or later, Power Query is pre-integrated so, you can skip Step – 2, 3, 4, 5.
  • If you are using Office 2010 or Office 2013, need to follow all procedure carefully.
  • If you are using Mac, then you can try it with Power BI Desktop or other Mac tools.

Step2: Go to your browser and Download Power Query as Add-In from Microsoft Official website. (https://www.microsoft.com/en-in/download/details.aspx?id=39379)

Note: Select your appropriate language and hit on Download button

Step3: Select one from first two according to your system architectures and hit on Download button.

Step4: Double click on your download file or press right click on it and select Install.

Note: Install it properly according to on screen instruction. And open Excel again.

Step5: Go to File Tab and select Options to open Excel Options dialog box.

Step6: Click on Add-ins button and choose COM Add-ins from Manage and hit on Go button.

Note: Check on `Microsoft Power Query for Excel` and hit on OK.

Step7: Click on Data Tab and select New Query to choose From File > From CSV.

Note:

  • You can select any option according to your dataset format; it may be an Excel file, JSON or any other file format that holds your data.
  • You can connect to online server.
  • There are multiple supporting data source types.

Step8: Import Data Prompt box will be open to select your text or csv file.

Step9: After selection of dataset, It will pop up a window for Load OR Edit. Click on Edit to use Power Query.

Step10: Now, you have successfully entered in Power Query.

Note: You can perform transformation here like filtering, dropping errors, null values etc.

Now, we are starting to access Power Query in Power BI Desktop

Procedure

Step1: Open your Power BI Desktop on your Device

Step2: Click on Get Data button in Home Ribbon and select Tex t/CSV.

Note:

  • You can select any option according to your dataset format; it may be an Excel file, JSON or any other file format that holds your data.
  • You can Scrape data using python also or connect to online server.
  • There are more than 160 supporting data source types.

Step3: Open Prompt box will be open, to select your text or csv file.

Step4: After selection of dataset, it will pop up a window for Load OR Transform Data. Click on Transform Data to use Power Query on Power BI Desktop.

Now, we will learn what is the method/process to add data from different sources in Power Query in Excel.

Connecting to a File

Step 1: Open Excel

Step 2: Click on Data tab and Select New Query.

Step 3: Now Opt From File > From CSV

Step 4: Navigate the CSV file and hit on Open

Step 5: In Navigator window, preview the dataset and hit on Edit.

Connecting to a Database

Step 1: Open Excel

Step 2: Click on Data tab and Select New Query.

Step 3: Now Opt From Database > From MySQL Database

Step 4: In MySQL Database dialog box, type server name (e.g. localhost:3306) and database name that you want to choose.

Step 5: In the Access a Database, type username and password of your mysql

Step 6: Hit Connect.

Step 7: In Navigator dialog box, Select one or multiple table and hit on Edit.

Connecting to a Azure SQL Database

Step 1: Open Excel

Step 2: Click on Data tab and Select New Query.

Step 3: Now Opt From Azure > From Azure SQL Database

Step 4: In Microsoft SQL Database dialog box, type server name and database name that you want to choose.

Step 5: In the SQL Server Database, type username and password of your database

Step 6: Hit Connect.

Step 7: In Navigator dialog box, Select one or multiple table and hit on Edit.

Connecting to Other Sources (Web)

Step 1: Open Excel

Step 2: Click on Data tab and Select New Query.

Step 3: Now Opt From Other Sources > From Web

Step 4: In From Web dialog box, type url: https://en.wikipedia.org/wiki/Cricket_World_Cup

Step 5: In Navigator dialog box, Select one or multiple table (Teams’ performances[edit]) and hit on Edit.

Now, we will learn what is the method/process to add data from different sources in Power Query in Power BI Desktop.

Connecting to a File

Step 1: Open Power BI Desktop

Step 2: In Home tab and Select Get data.

Step 3: Now Opt Text/CSV

Step 4: Navigate the CSV file and hit on Open

Step 5: In Navigator window, preview the dataset and hit on Transform Data.

Connecting to a Database

Step 1: Open Power BI Desktop

Step 2: In Home tab and select Get data.

Step 3: Click on More… and choose MySQL Database in Get data dialog box.

Step 4: In MySQL Database dialog box, type server name (e.g. localhost:3306) and database name that you want to choose.

Step 5: In the Access a Database, type username and password of your mysql

Step 6: Hit Connect.

Step 7: In Navigator window, Select one or multiple table and hit on Transform Data.

Connecting to Web

Step 1: Open Power BI Desktop

Step 2: In Home tab and Select Get data.

Step 3: Now Opt Web

Step 4: In From Web dialog box, type url: https://en.wikipedia.org/wiki/Cricket_World_Cup

Step 5: In Navigator dialog box, Select one or multiple table (Teams’ performances[edit]) and hit on Transform Data.

Conclusion

If you like this blog, you can share it with your friends or colleague. You can connect with me on social media profiles like LinkedIn, Twitter, and Instagram. If you have any doubt feel free to comment.

Leave a Reply