cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cshowe80 Regular Visitor
Regular Visitor

Ghost BLANKS showing up in reports

I'm hoping for some help here as I am pulling my hair out. I've been trying to showcase Sales Forecast and Weekly sales by team in a report I've been working on. For some reason one of the teams is not being detected properly. In the Query Editor it clearly shows 26 entries for this team when filtered. Then when I'm adding elements to the Report it only shows 1. 

 

I am trying to show Total for this team as a "Card" visualization. I am lost here and I'd love some help if possible. 

 

Things I've Checked:

- Went over all entries to ensure exact naming match

- Reviewed my Excel file with the Raw Data and confirmed no blanks

- Checked the Query Tool and found 26 Entries for this team

- Made sure all data feilds were set (dates as dates, currency as currency, etc)

 

Report Design.PNGReport Design

 

 

 

 

Programmatic Count.PNGReport Visual Filter

Programmatic Entries.PNGQuery Editor DataProgrammatic Team.PNGTeam Mapping for Programmatic Team

5 REPLIES 5
Super User
Super User

Re: Ghost BLANKS showing up in reports

Do you have any relations between tables? In this scenario there coule be blank in case value is not found in related to table.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Super User
Super User

Re: Ghost BLANKS showing up in reports

as @parry2k says, if you have orphan records (records in your fact/data table with no matching record in the lookup/dimension table) you will get this behaviour.

 

to check for this, create a table on a new report.  Place a slicer on the dimension table and select the blank record.  Add the foreign key from your data table to the visual.  Add any other column in the data table to the values section (eg count of a text column is good). This will show you teh orphan values



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
MarcelBeug Super Contributor
Super Contributor

Re: Ghost BLANKS showing up in reports

Another possibility is that you have data in a column that is incompatible with the data type of your column, e.g. decimal numbers in a column with data type "Whole Number" (= Int64.Type), which is possible e.g. if you provided a data type with Table.AddColumn.

 

In this situation, the data will be visible in the Query Editor, but won't load into the data model.

Recently I rased an issue for this.
Microsoft explained that the column types are advisory and don't guarantee that the actual values will have this type.

Specializing in Power Query Formula Language (M)
cshowe80 Regular Visitor
Regular Visitor

Re: Ghost BLANKS showing up in reports

Thanks for the insights gurus. I have relationships and I was under the impression I had a handle on how to use them but perhaps I'm going to have to go back and do some more practical research and work. I've been trying to use them like "baked in VLOOKUPs to change computer names to friendly names. The system uses the format below and I wanted all the reports to show as First Names & Last Name; Ben Decham in my example. 

 

Example

Ben Descham(ben.descham@acme.dfp) (ben.descham@acme.com)

 

Am I wrong in thinking that I can create a sheet (lets call it Rep Name) with the unique system names for all the sales reps in the 3 report sheets (Forecast, Budget, New Sales) and map it to their friendly name as with Ben above? My Rep Name sheet has System Name, Friendly Team and Sales Rep Name. I think the Team name is the only value that would not be unique as many unique users are assigned to each team. 

 

This is showing me how embarrsing my skill level is at PowerBI. Just when I thought I was making good headway I hit the wall. Here is the screen grab of my spaghetti relationship tables. I've also included the report layout in case that helps. 

 

 

 Relationships_Sales_Report.PNG

Super User
Super User

Re: Ghost BLANKS showing up in reports

You can do what you say, but it is not the best approach.  You shold make these mapping changes during data load rather than after data load. 

 

Read my article here http://exceleratorbi.com.au/shaping-modelling-power-bi/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 316 members 3,368 guests
Please welcome our newest community members: