Forum Discussion
Large numbers not pulling/displaying when refreshed online
I have a BI dataset which refreshes from a local microsoft sql server using a gateway. The table has 2 fields in it which are bigint, and in BI they are whole numbers. They seem to be maxing out as they they are int when refreshing online however. If I open Power BI Desktop and refresh my dataset everything comes through properly and I see e.g. transaction number 2164903008. If I publish this to web, I similarly can see the transaction number online in the report or when analyzing the dataset in excel. However, once I refresh online (incremental refresh is enabled for this dataset, if that matters), large transaction numbers such as that no longer display and instead everything above a certain point is blank. Even looking at a pivot report in Excel where I have a filter on that transaction number, i can be viewing that, refresh from app.powerbi.com and then refresh my excel pivot report and that transaction number disappears. Everything is working in SQL Server, and everything is seemingly working in Power BI Desktop.
Anyone know why this limit is existing and what I may be able to do to resolve on the web?
2 Replies
- GilbertQ
Super User
Hi Anonymous
I would make sure that in your PBIX you specify the data type as Whole Number (this defaults to a bigint by default)
I have never had an issue once i have set the data type to whole number, even with incremental refresh.
I have indeed set these fields as whole number in pbix. The only thing 'weird' that I've done is that these were originally int in the SQL table. The numbers eventually got too large for int so I changed the fields in the sql table to bigint. Everything looks fine in sql server, and the pbix in power bi desktop does have the field populated; it's just after refreshing the BI service online that things are null. Even re-publishing the pbix to cloud does not resolve though so I'm not sure why that would be. Is there any way that the online service had these fields as int somehow in the background even though they have always been whole number? The only thing I can think to do is try to publish as a new dataset to see if that solves it and there's just some lingering thing associated with this set. But I'd really prefer not to do that since over 100 people have access to this set and I'd have to migrate all of the users and train them on making sure they select the correct dataset.
