Forum Discussion

dMoneyZA's avatar
dMoneyZA
Frequent Visitor
9 years ago

Gateway refresh messes up my data

I've recently tried to implement the on-premise data gateway for a sales/budget report I made that is based on a series of excel files.

 

I eventually managed to get it refreshing through the gateway without any errors. However, now when it refreshes using the gateway it seems to change the format (or understands the format) of my sales figures differently.

 

You see in PBI Desktop my sales figures have a comma as a thousands seperator and all my figures display perfectly. If I were to manually upload it to PBI Service (without configuring a gateway) it works fine. But as soon as I configure a gateway and try refresh using that it seems to read the thousand's separator as a decimal point. Resulting in my figures all going haywire.

 

I have attempted to remove the thousands seperator in PBI Desktop and reupload but I am still having the same problem.

 

Anyone know how I could solve this?

4 Replies

  • alanhodgson's avatar
    alanhodgson
    Icon for Solution Supplier rankSolution Supplier
    9 years ago

    Hey dMoneyZA,

     

    I would look at the data format of the columns in your excel files and compare it to the data format when imported to PBI Desktop. The data format in Desktop should stay the same when it is published to the Service.  (I would test this before manually uploading the files to PBI Service)

     

    Also, how is the data refreshed from the excel files? (OneDrive, Sharepoint, etc.)

     

    There was a bug that was fixed in early 2016 that was causing the thousands seperator to show as a period. You can read more about it here.

     

    Hope this helps,

     

    Alan

    • dMoneyZA's avatar
      dMoneyZA
      Frequent Visitor
      9 years ago

      Hi alanhodgson

       

      Thanks for the reply.

       

      The data format in my excel files was the 'General' format which loaded the same when imported into PBI Desktop (I subsequently do some transformations and eventually change it to the 'fixed decimal' format. I did try manually changing the format in Excel to the 'Currency' format and seeing what it looks like when imported into PBI Desktop. Alas it imports it as that 'General' format.

       

      How is the data refreshed. Well the excel files are located on our server and before yesterday (since I was on the free version) I was manually refreshing the report in PBI Desktop before re-uploading it to PBI Service. Which all worked perfectly. However I wanted to see what the Pro version could do for us so started my Trial of it. I installed the on-premise data gateway on my desktop and managed to set it up fine save for some teething problems. Suffice to say, running the 'Test Connections' option under Manage Gateways gives me no errors; and the actual data gets refreshed fine (save for the format which you shall notice soon).

       

      Now, If I upload the report from PBI Desktop it looks like so:

       

       

       

       

       

       

       

      As soon as I refresh the dataset on PBI Service (that uses the gateway) my report changes to this:

       

       

       

       

       

       

       

      As you can see, it is now off by a magnatude of 1000.

       

      Thanks for the link to the past problem, but unfortuneatly all they say in all those links is that it was fixed in a previous build. My PBI is up to date so that shouldnt be a problem either.

       

      Any further insight into this would be much appreciated.

      Thanks

       

       

      • v-caliao-msft's avatar
        v-caliao-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi dMoneyZA,

         

        I have tested it on my local, I refreshed the dataset by using "REFRESH NOW" and "SCHEDULE REFRESH" and we cannot reproduce this issue.

        Could you pleas provide us more detail information, so that we can reproduce this issue and make further analysis.

         

        Regards,

        Charlie Liao