My Report turn it blank in service

[Anonymous]

Hi, I have a report with this datasource

1. Salesforce Reports
2. Excel files stored in SharePoint (owned by me). One of this files is a PowerQuery from an external site. I cannot do direct query in Power BI because file in web are stored in .xls format

When I work Desktop report, it refresh normally, and do not display any issue. But when I charge in Service, part of data turns it blank and stop to work

I have downloaded from Power BI Service report, and appear with empty fields, but when I refresh downloaded report, it works properly again

Anyone knows why this happen?

Report in PBI DesktopSame Report in PBI Service

[v-jiascu-msft]

Hi @Anonymous,

I think this can't be a DirectQuery model. Even refresh stopped, we still can see the data that already exist. So maybe something is wrong with the formulas or the visuals.

Could you please post your formulas of the fields that went wrong? Are there any error messages?  Did you use any custom visuals?

Best Regards!

Dale

[Anonymous]

Hello
Thanks for answer.
Issue isn't in visuals, formulas or measurements, because thoses don't report some error message.

I merged two tables to obtain Columns 'TRM' and 'IVA'. Here is the view in Query Editor

But when I load and review Table, these fields, TRM and IVA,  appear empty 

This issue occurs only when I dowload PBI Service Report with blank information. But when I work in PBI Destkop, file do no have update troubles

In Original desktop version file, works properly. Failure comes when I load the file to Service.

Those are the sources

But when I store source table 'TRM' file in my PC, I haven't this problem. 

This, only works in PBI DesktopThis works both of them

I want understand why occurs, because I need store all my files in SharePoint

Best regards

[v-jiascu-msft]

Hi @Anonymous,

It's hard to reproduce this issue. Could you please check these below:

1. Does the sheet of the excel with Power Query have data?

2. There are three columns turned blank. How about their source table? Are they blank too?

3. Could you please post the formula of "Won" and "Attainment"?

Best Regards!

Dale

[Anonymous]

Hi, @v-jiascu-msft thanks for your answer

Yes is a really strange issue ..

1. Does the sheet of the excel with Power Query have data? Yes, it does. How I explained, report works normally in Power BI Desktop.

2. There are three columns turned blank. How about their source table? Are they blank too? No, in the query editor I can see data in this columns. These are result to 'Merge Query' and in query editor works properly.

3. Could you please post the formula of "Won" and "Attainment"? Won Rev = CALCULATE([Precio Total USD];'Cuotas'[Stage]="Ganada en proceso") Attainment = IFERROR( [Won Rev] / SUM('Cuotas'[Cuota]);0)

[v-jiascu-msft]

Hi @Anonymous,

This is weird. Let's do some trouble shooting.

1. Upgrade Power BI Desktop to the latest version: download;

2. Are the three columns totally empty or partially empty? (we can check it by sorting)

3. Remove "iferror" from "Attainment". Maybe we could see what error it is.

Maybe you need to create a ticket here: https://powerbi.microsoft.com/en-us/support/pro/

Best Regards!

Dale

[Anonymous]

Hi @v-jiascu-msft thanks, I follow your instructions 

About your questions, columns are totally empty. I change the 'attainment' mesure, but when I charge report in Service, trouble is again there. 

I suppose that is relationated with source in SharePoint. But just occur with an only file, when I change file location to pc, it works. 

I just want have all files in same location.

I will be proceed to create a ticket.

Thank you for your attention

[Anonymous]

Hi 

Someone can help ? 

thanks ! 

[Greg_Deckler]

Are all of the 0 or NaN numbers related to a single table and is that table generated from the external site that you reference?

[Anonymous]

Hi, all the numbers mentioned are related in same table. But this table have source both sites: Salesforce and Sharepoint. I merged the information to generate measurements.
I found that when I save query file locally in PC, it work properly, but when I change file to SharePoint not.
I want have all files in SharePoint, because allows schedule refresh without gateway and pc must not stay online all time. This is the only report where it not works.

[Greg_Deckler]

Hmm, OK I believe I understand this. Let me make sure. You have a report with both a Salesforce data source a file data source (Excel, CSV, etc.) that gets merged into a single table. I am assuming you have a query for Salesforce, a query for the data source file and a merge query? If the data source file is local everything works (Desktop and Service), if it is placed into SharePoint, it does not work when in the Service but does in Desktop. Is that all stated correctly?

[Anonymous]

exactly

But , I have several excel files in SharePoint, and only one of them have this problem.

This file is different to theothers because it is also a Power Query File, connected to an excel file in an external website. I worked this way because source excel file is updated frequently.

I couldn't connect directly Excel external web site to Power BI, because source file is in .xls format, and Power BI only support .xlsx

Hope this can be more clear !