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

[Paulo_Abreu]

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 publishing) values get garbled or just get null

Server (the machine I have PBI Desktop and On premises Gateway) Region is Portugal, locale settings default, "," as decimal separator, "space" as thousands. Aparentely the gateway is not reading correctly this or some setting for Region on tenant I am missing? Re-installed on premises gateway, the closest region I see is "West Europe", let it be as it was. For now I have the on premises service stopped so that it doesn't update data and users can acess PBI service to check the report correctly.

Will I have to change the Region settings on server to US and review the transform data accordingly or, what else?

Thanks 

[Paulo_Abreu]

Hi again, just forget this last message, problem remains the same. The error above was due to very obvious reason (no access to share from where I was... dahhh)

[GilbertQ]

Hi @Paulo_Abreu 

Can you make sure that you can access that share from the Gateway (also make sure that you log in with the same account which you configured in the PBI Service)

[GilbertQ]

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]

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]

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 · Accepted solution]

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