1

I have a problem:

I have a PBI file containing three data sources: 2 SQL Server sources + 1 API call.

I have separate queries for each respective data source and an additional query that combines all three queries into a single table.

Both SQL Server sources have been added to a gateway and I can set scheduled refresh for each source, if I publish them in separate PBI files.

However, I cannot set scheduled refresh for the file that contains all three sources - both the data source credentials and scheduled refresh options are greyed out.

The manage gateway section of the settings page also shows no gateway options. If I publish the SQL Server data (with no API data) I can clearly see my data source and gateway under the gateway heading.

screenshot of dataset settings

Does anyone have any idea why this might be happening?

Thank you,

Toots
  • 11
  • 1
  • 2

2 Answers2

1

I had the same problem. I have a PBI file with different data sources : SQL Server sources and APIs.

On The PowerBI Service the Data Source Credentials was grayed out, so here's what I did :

  • Downloaded the file
  • Refresh the file locally and signed up on all data sources (the Server of DB Server name changed but not for the APIs)
  • published in the PBI Service

It worked for me.

paul
  • 11
  • 1
0

Same problem here. After additional poking around I learned that the "Web Source" (API call) was the reason for the inability to refresh and can cause "Data Source Credentials" to be inaccessible. This was annoying to learn after diving down several rabbit holes.

Several (weak) workarounds

Using Excel's Power Query to connect to the web source. Learn more about Excel's Power Query. Make any needed transformations.

  1. Put the Excel file in SharePoint Online folder or other PBI accessible directory.
  2. Connect to the Excel file using the appropriate data source (i.e. SharePoint Folder).

Alternatively, if the data is static, you can directly copy/paste values into PBI (if you just need to get this done and move on with your life):

  1. Copy target values

  2. Open Power Query Editor

  3. Home tab -> Enter Data

  4. Paste values into table

Hopefully this will save some poor soul a little of their life.

HamiltonPharmD
  • 582
  • 3
  • 19