Data missing between query and report

[Anonymous]

Hello,

I'm working on a project that involves bringing together several different billing files across 2016/2017 with about 2.6M lines of data. Power BI has been great for processing all this and giving us the abillity to analyze trends on the fly.

However, we have noticed that there is a good chunk of data missing. I can drill down on it in the Query Editor, but on my report it is missing. Below is the query editor results and a table visualization from the report.

We have five main columns that we look at: Vendor Invoice Amount, Disbursed Amount (funds paid to the vendor), Client Billed, Client Paid, and Client Unpaid. 

With a sample size of 38k rows, totals match EXACTLY for all columns but Client Unpaid. For some reason it is the only one missing data that we've been able to identify. I've drilled down to the offending lines on the report and exported, but have not been able to find any reason why they should be missing.

[v-jiascu-msft]

Hi @Anonymous,

It seems you create a measure of "client unpaid". Could you please share the formula? If I am wrong, how does the data look like in the data view?

Best Regards!

Dale

[Anonymous]

Hi Dale,

No, it is not a measure, simply a column. When the "Net Billed" column has a value, "Client Unpaid" should have something there as well, even if it is only 0.

The first screenshot below shows another instance of the data not being there, this time in the Data View. The second screenshot is in the Query Editor, drilled down to the same place. 

And this is what I find confusing. The cell is populated when viewed in the Query Editor, so we know the data is being properly loaded into Power BI Desktop. But then it's suddenly missing in the Data View and the Visual View??? The only steps taken in the Query Editor are unreleated to this particular column.

Furthermore, as you can see in the first set of screenshots I uploaded, there are plenty of values in the Client Unpaid column. It almost seems as if the missing data is completely random. 

This will completely ruin the project if we cannot figure out why this is happening and so far there are no indicators.

[Anonymous · Accepted solution]

So I actually just stumbled upon the answer. For some reason, the other four columns we look at were all being identified by Power BI as data type Decimal Number, while the Client Unpaid column was being identified as data type Whole Number. I haven't double checked yet, but I believe any amount that had pennies was being excluded.

Thanks for your time,

Seth