Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cshowe80
Helper I
Helper I

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 DesignReport Design

 

 

 

 

Report Visual FilterReport Visual Filter

Query Editor DataQuery Editor DataTeam Mapping for Programmatic TeamTeam Mapping for Programmatic Team

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

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)
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.