DataFormat.error when refreshing in Power BI Service

[ThuJa23]

Hi everyone, 

I am doing a report which carries many data sources (access file, excel files and D365CRM). The report is done in Power BI Desktop and refresh without any error. I uploaded the report to PBI Service and setup the Data Gateway, all works well as below

Then I set up refresh schedule and try to refresh the data. But I got the failure when refreshing with below notification

As I understanding the error alert a problem from access file but I checked once again the file in Power BI Desktop, try to fix all the format and refresh in Power BI Desktop. No any errors there. I uploaded again new saved file to same workpace, and set up again the data gateway. When I refresh, it still appears. 

Could you please help me in this case? I do not know where to fix this problem. 

Thanks so much

Thu

[v-xiaoyan-msft]

Hi @ThuJa23 ,

In the course of our investigation, we found a similar thread. OP solved this problem in several ways.

• Set Privacy Level to Organizational. Make sure you have the same privacy level security in both Power BI Service and Power BI Desktop.
• Made sure all reports are refreshed at different times
• Created another on-premise gateway to split the load
• Created database tables rather than views to reduce processing between PowerBI and MSSQL
• Ensured both desktop and gateway has the latest version installed at all times

For more information, you may refer to:

'Unable to connect to data source undefined' 

PowerBI intermittent Refresh Failure - Unable to connect to the data source undefined 

Best Regards,
Community Support Team _ Caitlyn

[Trelobar]

Hello,

I have the exact same problem. 3 Access Databases are connected over the Gateway administered through our IT. After the first attempt the Gateway server couldn’t open the Access database, Access Database Engine was missing. It was installed and tested. Now I get the same error as the OP.

DataFormat.Error for every Database. Tried the solutions from @v-xiaoyan-msft, nothing worked. Searched for table format errors in PowerBi Desktop but there seems to be everything fine. The Access Databases are imported as File.

[ThuJa23]

Hi, 

The problem is still there, even I uninstalled and reinstalled with the newest updates of PBI and Data Gateway

 @v-xiaoyan-msft do you have any other advices?

Thanks so much

Thu

[v-xiaoyan-msft]

Hi @ThuJa23 ,

Based on the above information, if you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

[Trelobar]

Hello @ThuJa23 

I finally solved my problem, hope it also helps you. My access file is saved on a server. I can access it as a network drive. In the explorer you see the network drive with the folder name and in brackets the server address. I assumed that the server address is the one in the brackets, but it was false. Right klick on the network drive, settings, and under the “DFS” tab is the right address.
Changed the address in the gateway and in PowerBi Desktop and now it refreshes over the online service.
Hope it helps you.

[juleshorton]

Hello @Trelobar 

Could you elaborate, please? Where are you changing the address? On the server? In PBI?

[Trelobar]

@juleshorton you change it in the Report settings one the File tab on the upper left side.

Once you added the datasource, you change the source path for the access document to the right address (full servername with path)

My problem was that then I added the file, stored on our server, to Power BI. It saves the local path saved on my computer (M:datasource/file.accdb)
The onlineservices can't find the file because the full serverpath is false. I changed it to the serverpath and now the onlineservices are able to find the file. (\\servername\filestorage\datasource\file.accdb)