Forum Discussion

Paulo_Abreu's avatar
Paulo_Abreu
Frequent Visitor
3 years ago
Solved

Reload data problem via on premises gateway (Excel, Portugal locale)

Hi, need some help here, TIA!   Folder data source, excel files, column with decimal value converts fine, everything OK in Power BI Desktop Publish to Service, when it refreshes data (just after p...
Need Help
  • Paulo_Abreu's avatar
    Paulo_Abreu
    3 years ago

    Update, problem solved!

     

    For some reason PBI service has a problem dealing with "xls" compability mode files. Saving them as "xlsx" solved the problem, now its updating and showing data correctly

     

GilbertQ's avatar
GilbertQ
Super User
3 years ago

Hi Paulo_Abreu 

 

I have not seen this happening before.


Can you give some examples of what it is in PBI Desktop and then what it looks like in the PBI Service?

 

Also can you make sure you have got the latest version of PBI Desktop installed. Then open the file and publish to the PBI Service.


Can you also make sure that you have got the latest version of the On-Premise Gateway installed?

Paulo_Abreu's avatar
Paulo_Abreu
Frequent Visitor
3 years ago

Hi GilbertQ 

 

Latest versions of PBI and gateway, this is a fresh new installation, latest downloads.

 

Let me elaborate a little more, maybe there's a clue:

When loading the excel tables I was getting an error converting the "sales" to decimal because the numbers had a "." separating thousands (a "," as decimal value) - this was strange since the version I had on my PC didn't exhibit that - so I replaced the "." with null on that column and numbers converted and showed correctly (PBI Desktop). After publishing the reload imediately showed a problem, these values were abnormally high, like 10x.

I downloaded the .pbix and noticed the numbers were inflated like 10x, 8,20€ was showing as 820,00€ and so on. Only then I realized the "." was due to the locale region on server which was "Portugal (Pre-reform) or something like that, this format implied dots "." as thousands, so I changed the server to Portugal normal and it was ok, no more "." so I removed the step that was replacing these values. So, PBI desktop is fine (as it ever was), but afther publishing on service now all the values are gone, null.

 

Bottom line: PBI Desktop perfect, refresh via Service is messing things up. My logic is they should be the same, unless something at the gateway level should be set differently, which I can't figure.

 

I can try setting the excel column as number before reloading and see if this solves the problem... ?

 

Thanks

  • Paulo_Abreu's avatar
    Paulo_Abreu
    Frequent Visitor
    3 years ago

    Update!

     

    Hi again, Sorry, problem now has nothing to do with the column data or what I posted above, I thought it was because of the 1st problem but it isn't - sorry for that

     

    Problem now with the empty value is because the gateway can't access the network path where are the excel files, it complains with a DatasSource.Error I/O error 67 acessing the "Example file" on path "\\servername\Folder\ExampleFile.ls" ... Details: \\servername\folder

     

    It works internally and gateway has the correct permissions... if anyone has a light ... but I'll check this, maybe something very obvious.

     

    Thanks

    • Paulo_Abreu's avatar
      Paulo_Abreu
      Frequent Visitor
      3 years ago

      Update, problem solved!

       

      For some reason PBI service has a problem dealing with "xls" compability mode files. Saving them as "xlsx" solved the problem, now its updating and showing data correctly