Network drive - 'unable to connect to data source undefined' error

[Anonymous]

Hey all,

I've been struggling with the error message in the screen shot below (please excuse the mix of Norwegian and English) since the beginning of the year. 

For context: I'm working with a client that uses a network drive for shared organizational files. I use some of those files as data sources in my Power BI model, but I need to change what file I am using once a year. For example, one of the files is a yearly budget in Excel, and a new one is prepared every December. Then it is regularly updated throughout the year (i.e., several times a week). 

As expected, in the beginning of January I had to change which file I am trying to connect to in Power Query. I did so, using a parameter to switch between the use of IP address in Power BI Desktop and server name in Power BI Service (the network drive is located on an on-prem server). Unfortunately I started seeing the below error message when I tried updating my dataset, and soon after my scheduled refresh stopped working. This is detrimental to my report because it is crucial for my client that the data is as up-to-date and "live" as possible (8 refreshes per day) - which it's not right now.

Can anyone help me with this? I'm using a Power BI Gateway which has been updated to the latest version (Dec 2020). Neither the server nor the files I'm trying to access have moved or been renamed. The privacy level settings should also be the same in both Desktop and Service -- and that's all the tips I've been able to find on Google so far.

Error message in Power BI Service
EDIT: When scheduled refresh is working, my report actually seems to be displaying new data as expected. However, the problem arises when scheduled refresh stops working.

[Anonymous · Accepted solution]

Hey, @v-easonf-msft 

I got it to work by switching from using the Folder connector to using the Excel connector in Power Query instead. I also re-entered my credentials like you suggested. Thanks!

[Anonymous]

@v-easonf-msft - thanks, but I've just checked, and everything works fine on Power BI Desktop/Power Query. That goes both for my original pbix file and when I download the report from the service. I'm only seeing the error message in the web portal. However, when I try to re-publish my report to the Power BI Service, my visualizations are showing new data as expected even though I still see the error. What I need help with is ensuring scheduled refresh starts working again, as now it's disabled after 2-3 refreshes fail.

PS: For clarification I am keeping my data model and my report with visualizations in two separate pbix files. I am using the Power BI dataset connector in my report, in order to keep it up-to-date with the newest data.

[v-easonf-msft]

Hi ， @Anonymous 

If you did not change the excel file and everything is normal when you refresh the report on the desktop, I will suspect that your excel credentials in the gateway have expired. You may need to re-enter your data source credentials, even if it  seems to be working fine.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973]

Hi @Anonymous 

The Gateway is somehow sensitive to manual changes every now and some mismatch can occure down the path. I suggest that first you update your Gateway, Publish your report into a new workspace and use the Gateway to manually refresh it. If all is good then check by elimination what is going wrong in the previous Workspace and correct it if you still want to use it.

[v-easonf-msft]

Hi ,  @Anonymous 

I agree with @collinq , this error mostly occurs when the structure of the original data source does not match the structure of the report model ( therefore cannot access the corresponding data).

You can  download the report from the service and   refresh  it on PowerBI Desktop to find more error message.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Thanks, @aj1973. How do I check which credentials the gateway is using to connect to my Excel file? As far as I know I'm using the same credentials I've used before - that is, the credentials to the network drive. Those would not have changed since I last tried to connect to other similar files in the drive.

[collinq]

Hi @Anonymous ,

This is commonly an issue of "something is different" .  As in, an extra field, or, more likely a deleted or renamed field.  here is more lenghty explanation of this issue: Chris Webb's BI Blog: Understanding The “The key didn’t match any rows in the table” Error In Power Query In Power BI Or Excel Chris Webb's BI Blog (crossjoin.co.uk)

[aj1973]

Hi @Anonymous 

It's more likely the Gateway can't find the right credential to connect to the table "Budjett PW 2021.xlsx".