On-premise data gateway (personal mode) - missing data after refresh

[lT-Guy]

I have some PowerBI reports which depend on the data stored in Excel sheets which are stored on-premise.

To avoid every day manually updating the report in the Desktop App and publishing it to the Teams Workspace, I installed the On-Premise data gateway (personal mode) and set a refresh shedule. The gateway status is displayed as online and the sheduled task is running as planned. In the "Resfresh history" there are no errors or warnings, every scheduled or on demand refresh is shown as "completed".

But all data colums except one which are used in a slicer part in the report seem to contain no data after the refresh, as the slicers all show only "Blank" as the only value. In the log files of the gateway I can not find anything helpful.

When I compare the values that the report shows in the diagrams, like item counts, these are completely updated during the refresh, but the values used in slicers show only "Blank" as the only value.

When updating the report in the Desktop App and publishing it, all works fine.

Has anyone experienced such strange things?

The gateway version is 14.16.6584.1 and the PowerBI License is Pro.

Regards, Gerhard

[v-yuezhe-msft]

@lT-Guy,

Which specific Excel sheet does the field that you use to create the slicer come from? Is there any possibility that you can share the Excel to me? I will test it in my enviornment.

Regards,
Lydia

[lT-Guy]

Hello @v-yuezhe-msft

Our Excel sheet has 19 columns with about 220000 rows.

The columns that are always imported contain only numbers, integer or floating point.

In the other ones there is text mostly formated "nnnnnn - xxxxxxxxxxxx" where "n" are numbers and "x" is a string.

It looks like the columns containing strings are not imported correctly, and are empty.

I can not share the original data sheet, but if it helps an additional sheet, which is used for matching postal codes to geo-data. There it looks like it is also only numerical data imported.

Best regards, gerhard

[v-yuezhe-msft]

@lT-Guy,

You can share dummy data to us.

Regards,
Lydia

[lT-Guy]

Hello @v-yuezhe-msft

I have prepared a dummy data set in the meantime and did some testing with it. The Excel sheet has the same columns as the live data set but far less and of course meaning less data in it. I first thought that the volume of data is the problem. Because I have reports depending also on Excel sheets but with only 1 MB in size. There the update process using the On-Prem gateway is working fine. I tested wit the existing report and also created a new report based on the dummy data set. With the existing and the new report, refreshing the dataset using the gateway destroys most of the data in it. Only refreshing the report in the desktop application and publishing it to the Power BI Workspace results in a fully working report.

I will try to share the Excel sheet through OneDrive with you, as I could not find a option for attaching a file.

Best regards, Gerhard