Data Source Credentials Setting Not Visible for Dataset

[SlothB77]

I created a new Dataset based off a File from a Sharepoint Team Site.  I imported Excel data into Power BI so that the data is automatically refreshed from SharePoint.  I did this twice.  For the first file, under settings I can see:

• Gateway connection
• Data source credentials
• Parameters
• Scheduled refresh
• OneDrive refresh
• Q&A
• Featured Q&A questions
• Endorsement

Once I configured the Data Source Credentials, I can schedule a refresh from sharepoint.  For the second file, I can only see the following settings:

• Parameters
• OneDrive refresh
• Q&A
• Featured Q&A questions
• Endorsement

I can't configure the data source credentials and I can't set up a Scheduled Refresh.  If both data sets were created the exact same way, what would explain why my permissions and settings different for each? 

[v-xicai · Accepted solution]

Hi @SlothB77 ,

For the first file,  the dataset may only contain on premise data source,  then it needs to use the gateway,  and you can configure schedule refresh, see more: :On-premises data gateway , Configure scheduled refresh .

For the second file, the dataset may only contain cloud data source,  then it needn't to use the gateway and configure schedule refresh. Power BI refresh will synchronizes datasets and reports with their source files. By default, Power BI refresh every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization. See more: https://docs.microsoft.com/en-us/power-bi/refresh-data#onedrive-refresh .

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SlothB77 · Accepted solution]

We are using Sharepoint Online.

I have resolved the issue. Here is how I was able to resolve:

1. I opened the Excel source file stored on SharePoint from SharePoint.
2. I went to Data > Manage Data Model
3. In the Manage Data Model window, I chose Refresh > Refresh All. Now the Data Model columns are in sync with the source queries and connections.
4. I saved my changes to the Excel source file back to SharePoint and closed the file.
5. I went back to Power BI and deleted the existing Dataset.
6. I recreated my Dataset by: Create > Dataset > Files > SharePoint - Team Sites > Import Excel data into Power BI pointing to the Excel source file on SharePoint Online.
7. When I click the button for an On Demand Refresh of the Dataset it refreshes successfully. I do not get the frustratingly vague 'Data source error Unable to refresh the model because it references an unsupported data source' error message.
8. I can see the Data Source Credentials settings for the Dataset and schedule refreshes.

Here's the funny thing, when I created a redundant dataset with the exact same setings pointing to the same file, it returns the 'Data source error Unable to refresh the model because it references an unsupported data source' error when I perform an On Demand refresh, I canNOT see the Data Source Credentials settings and I canNOT schedule refreshes.

[v-xicai · Accepted solution]

Hi @SlothB77 ,

For the first file,  the dataset may only contain on premise data source,  then it needs to use the gateway,  and you can configure schedule refresh, see more: :On-premises data gateway , Configure scheduled refresh .

For the second file, the dataset may only contain cloud data source,  then it needn't to use the gateway and configure schedule refresh. Power BI refresh will synchronizes datasets and reports with their source files. By default, Power BI refresh every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization. See more: https://docs.microsoft.com/en-us/power-bi/refresh-data#onedrive-refresh .

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[collinq]

HI @SlothB77 ,

You mention that both data sources are created the same way.  But, do both of them use "internal" versus "cloud" data?  You stated that you are using "SharePoint" but are you using SharePoint Online or local SharePoint?  If it is SharePoint Online that comes with O365 then you are cloud based and you do not need a gateway.  If the SharePoint is inside your firewall, the you need a gateway.  Therefore, even though these files are both Excel and both similar as they are in "SharePoint" the next question is whether or not they are in different types of SharePoint?  Second big thought is to where the data itself is coming from - is it within the local infrastructure or is it cloud based?  That will also affect the need for a gateway.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[SlothB77 · Accepted solution]

We are using Sharepoint Online.

I have resolved the issue. Here is how I was able to resolve:

1. I opened the Excel source file stored on SharePoint from SharePoint.
2. I went to Data > Manage Data Model
3. In the Manage Data Model window, I chose Refresh > Refresh All. Now the Data Model columns are in sync with the source queries and connections.
4. I saved my changes to the Excel source file back to SharePoint and closed the file.
5. I went back to Power BI and deleted the existing Dataset.
6. I recreated my Dataset by: Create > Dataset > Files > SharePoint - Team Sites > Import Excel data into Power BI pointing to the Excel source file on SharePoint Online.
7. When I click the button for an On Demand Refresh of the Dataset it refreshes successfully. I do not get the frustratingly vague 'Data source error Unable to refresh the model because it references an unsupported data source' error message.
8. I can see the Data Source Credentials settings for the Dataset and schedule refreshes.

Here's the funny thing, when I created a redundant dataset with the exact same setings pointing to the same file, it returns the 'Data source error Unable to refresh the model because it references an unsupported data source' error when I perform an On Demand refresh, I canNOT see the Data Source Credentials settings and I canNOT schedule refreshes.

[SlothB77 · Accepted solution]

We are using Sharepoint Online.

I have resolved the issue. Here is how I was able to resolve:

1. I opened the Excel source file stored on SharePoint from SharePoint.
2. I went to Data > Manage Data Model
3. In the Manage Data Model window, I chose Refresh > Refresh All. Now the Data Model columns are in sync with the source queries and connections.
4. I saved my changes to the Excel source file back to SharePoint and closed the file.
5. I went back to Power BI and deleted the existing Dataset.
6. I recreated my Dataset by: Create > Dataset > Files > SharePoint - Team Sites > Import Excel data into Power BI pointing to the Excel source file on SharePoint Online.
7. When I click the button for an On Demand Refresh of the Dataset it refreshes successfully. I do not get the frustratingly vague 'Data source error Unable to refresh the model because it references an unsupported data source' error message.
8. I can see the Data Source Credentials settings for the Dataset and schedule refreshes.

Here's the funny thing, when I created a redundant dataset with the exact same setings pointing to the same file, it returns the 'Data source error Unable to refresh the model because it references an unsupported data source' error when I perform an On Demand refresh, I canNOT see the Data Source Credentials settings and I canNOT schedule refreshes.