cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sethwallin
Helper I
Helper I

Data missing between query and report

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.

 

Capture.PNGCapture2.PNG

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.

1 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

View solution in original post

3 REPLIES 3
v-jiascu-msft
Microsoft
Microsoft

Hi @sethwallin,

 

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?

 

Data missing between query and report.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

 

Capture.PNGCapture2.PNG

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

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors