Welcome to the dynamic world of data analysis! In this tutorial, we'll dive into various techniques for connecting Excel Online to Power BI, opening a range of possibilities for your data arsenal. Amid these strategies, we'll briefly explore Kondado, a tool that can add an extra layer of efficiency to your integration journey. Get ready to unlock new levels of insights and productivity, transforming the way you handle data and reports. Shall we begin?
Centralizing Data in Excel Online with Kondado
- Creating Your Data Sources: Data Sources are the sources or tools from which you want to extract data to centralize in Excel Online. To register your data sources in Kondado, access our wiki and follow the respective step-by-step guide.
- Registering Your Excel as a Destination in Kondado: Now it's time to register Excel as a destination in Kondado to receive data from your tools. To register your Excel as a destination, follow the step-by-step instructions shown in our documentation.
- Creating Your First Pipeline: Now that your data destination and your data sources are registered, you can create your first pipeline. To create your first pipeline, follow the step by step guide in the platform.
Connecting Excel Online to Power BI Online
Alternative 1: Semantic Model
The first and most recommended method for those with a Power BI Service subscription is connecting Excel Online through a semantic model. To make this connection, just follow the steps below:
- Within your workspace in Power BI Online, click on “New” and then on “Semantic Model”:
- On the next screen, select the “Excel” option:
- Select the Excel file you wish to import and then click on “Import”:
- Done! Your Excel Online data will be available and refreshed in Power BI.
Alternative 2: Uploading OneDrive file
If you prefer not to use a semantic model, you can connect Excel Online to Power BI Online by uploading a OneDrive for Business file in your workspace:
- Within your workspace in Power BI Online, click on “Upload” or “Carregar” and then on “One Drive for Business”
- Choose the Excel online file created by the Kondado pipeline and then click on “Upload”:
- Done! Your Excel file will now be available on Power BI Service.
Connecting Excel Online to Power BI Desktop
If you do not have a Power BI Service (online) subscription or have a subscription but prefer to access your Excel data in Power BI Desktop, you can choose one of the alternatives below according the type of your Microsoft Account (organizational or personal) and the way you wish to make the connection.
Alternative 1 - Making the Connection through a Power BI Online Dataset
If you have imported your Excel Online file into the Power BI Service through a semantic model or uploaded the file in your workspace, you can consult it in Power BI Desktop by following the steps below:
- In “Get Data” select “Power BI datasets”:
- Select the Excel online file and then click on “Connect”:
- Done! Your Excel Online data that has been imported into the Power BI Service will also be available in Power BI Desktop:
Alternative 2 - Organizational Account: Making the Connection through a Web Link
If you do not have a Power BI Service (online) subscription and your Microsoft account is organizational, you can connect Excel Online via a web link by following the steps below:
- On the OneDrive web site, go to “My Files” on the left sidebar, locate the Excel Online file you want to connect, click on it, and then click on “Details”:
- In the left sidebar that will open, find the “Path”of the file and click on the icon to copy it:
- With the link copied, go to Power BI Desktop and click on “Get Data", and then select “Web”:
- Paste the link in the “URL” field and then click “Ok”:
- Log in with the same credentials used to access your OneDrive account, and then click to connect:
- Select one or more tables that you want to connect to your Power BI and click on “Load”:
- Note: In some cases, you might see two tables or tabs with the same name (with just a '1' at the end as the difference). If this happens to you, choose the tab whose icon has the blue detail, as shown in the example below:
- Done! Your table will be available for use in Power BI Desktop. Whenever the data is updated by Kondado in your Excel Online spreadsheet, it will also be updated in Power BI!
Alternative 2 - Personal Account: Making the Connection through a Power BI Online Dataset
If you do not have a Power BI Service (online) subscription and your Microsoft account is personal, you can connect Excel Online via a web link by following the steps below:
- On the OneDrive web site, go to “My Files” on the left sidebar, locate the Excel Online file you want to connect, click on it, and then click on “Embed”:
- Click on “Generate”:
- Copy the entire code that will be generated:
- Paste the code copied in the previous step in the box below and then click on “Generate URL”:
- Copy the link generated in the previous step, go to Power BI Desktop and click on “Get Data", and then select “Web”:
- Paste the link in the “URL” field and then click “Ok”:
- Log in with the same credentials used to access your OneDrive account, and then click to connect:
- Select one or more tables that you want to connect to your Power BI and click on “Load”:
- Note: In some cases, you might see two tables or tabs with the same name (with just a '1' at the end as the difference). If this happens to you, choose the tab whose icon has the blue detail, as shown in the example below:
- Done! Your table will be available for use in Power BI Desktop. Whenever the data is updated by Kondado in your Excel Online spreadsheet, it will also be updated in Power BI!