Forum Discussion
Power Bi Service - Excel sheet API
Hi everyone,
In Power BI Desktop, I download an Excel table via API. The update works without any issues.
The problem occurs when I save the file to Power BI Service. After that, I am not able to create the correct connection for regular update. Can anyone please advise me? Thank you very much.
Thankyou, Gabry for your response.
Hi OttoKok6,
We appreciate your enquiry through the Microsoft Fabric Community Forum.
Based on my understanding, the refresh is failing in the Power BI Service because the Web API data source referenced by the Excel connector has not been configured or mapped in the Service. It works in Desktop because the machine has direct access.However, in the Service the dataset requires the Web data source to be registered with the gateway and to have credentials assigned.
Please follow the steps below, which may help resolve the issue:
- In the Power BI Service, go to Settings and select Manage connections and gateways. Add the Web API endpoint as a Web data source and provide the same authentication method that used in Desktop.
- In the Workspace, go to Datasets and select Settings. Under Gateway connection, map the dataset’s Web source to the gateway data source created.
- Under Data source credentials, re-enter the credentials and save.
- Test using Refresh now, and then enable scheduled refresh.
If the API is publicly accessible, a gateway may not be required. In that case, simply set the correct Data source credentials in the dataset settings.
Additionally, please refer to the following links:
On-premises data gateway - Power BI | Microsoft Learn
Add or remove a gateway data source - Power BI | Microsoft Learn
Power Query Web connector - Power Query | Microsoft Learn
Configure scheduled refresh - Power BI | Microsoft LearnWe hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.
Thank you.
5 Replies
Hey,
I'm not sure about the exact error since the message is not in English. However, in order to use the API in Power BI, you need to connect it through a gateway. This means you have to configure the connection using a standard data gateway, which will allow the service to access the data source.Thankyou, Gabry for your response.
Hi OttoKok6,
We appreciate your enquiry through the Microsoft Fabric Community Forum.
Based on my understanding, the refresh is failing in the Power BI Service because the Web API data source referenced by the Excel connector has not been configured or mapped in the Service. It works in Desktop because the machine has direct access.However, in the Service the dataset requires the Web data source to be registered with the gateway and to have credentials assigned.
Please follow the steps below, which may help resolve the issue:
- In the Power BI Service, go to Settings and select Manage connections and gateways. Add the Web API endpoint as a Web data source and provide the same authentication method that used in Desktop.
- In the Workspace, go to Datasets and select Settings. Under Gateway connection, map the dataset’s Web source to the gateway data source created.
- Under Data source credentials, re-enter the credentials and save.
- Test using Refresh now, and then enable scheduled refresh.
If the API is publicly accessible, a gateway may not be required. In that case, simply set the correct Data source credentials in the dataset settings.
Additionally, please refer to the following links:
On-premises data gateway - Power BI | Microsoft Learn
Add or remove a gateway data source - Power BI | Microsoft Learn
Power Query Web connector - Power Query | Microsoft Learn
Configure scheduled refresh - Power BI | Microsoft LearnWe hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.
Thank you.
Hi OttoKok6,
We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.
Thank you.Hi OttoKok6,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.Hi OttoKok6,
We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.
Thank you.
