Reply
CalanFCfx
Frequent Visitor
Partially syndicated - Outbound

Table visual missing random values

Hello,

 

I've been using PowerBI for about 7 months - this is my first post.

I can't figure out why a table visual is showing only random data in one of my columns.

I've created a simple table visual to proof my data before turning it into a nicer visual, but it's missing values I know are there.

 

I have two tables - a labor table and a job details table.  They're related by the common column WOKEY (work order key).

When I add the job serial number from the job table, sometimes it misses the serial number.

The first 8 columns in this screenshot come from the labor table, and the AC_SERIAL column links on WOKEY to get that value from the job details table.

The second little table underneath is just a reference to show that it does work in some instances - some serial numbers are being recognized, while some are blank.

 

PBI1.gif

 

This is an extract of the PowerQuery showing that the missing values are in fact available:

 

PBI2.gif

 

So, in the first table above, the AC_SERIAL column should be showing Shop, Shop, Shop.

 

I've been searching the internet and these forums for days and can't seem to find an answer to this.  Based on what I've found in my searches, this is with I know / tried so far:

- the data is there - the required value exists

- WOKEY has been cleaned and trimmed in both tables

- WOKEY is formatted as text in both tables

- The AC_SERIAL column is set at Don't summarize

- I tried merging the job code table with the labor table to pull in just the serial that way, but it still comes up with random nulls under AC_SERIAL

 

What am I doing wrong?

 

Thanks so much!

 

 

 

1 ACCEPTED SOLUTION

Syndicated - Outbound

Unfortunately adding the RELATED column didn't help either - it still pulled in a lot of nulls where there is definitely values.

 

But, I persevered and finally found a work around.

 

My labor table is not tied directly to the job detail table - there's a couple more layers in between.  In a perfect world, this would be a single table and I would be able to create a hierarchy, but sadly the data source I have to work has it all divided into separate tables.  So it goes job detail to department (as 1:*), then dept to task (also as 1:*), then task to labor (also as 1:*).  Ultimately it still flows through the whole series of four tables as a continuous 1:* relationship.

 

But those couple extra layers seem to be the problem.  I tried creating a duplicate of the top level job detail table and reduced it to just the key column and the couple extra columns I need, then created an active relationship directly to the labor table (with an inactive relationship to the original high level job detail table).  Now when I pull in the required SERIAL column from the extra little job detail helper table, I don't get blanks anymore.

View solution in original post

5 REPLIES 5
mickey64
Super User
Super User

Syndicated - Outbound

I guess you use a 'Many-to-many relationship'.

Please read a URL below.

I would recommend you to use ’One-to-many relationships'.

 

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

Syndicated - Outbound

It's already a 1:* relationship (1 job code to many labor entries)

Syndicated - Outbound

How about adding a column to the table?

mickey64_0-1720543803184.png

 

Syndicated - Outbound

Unfortunately adding the RELATED column didn't help either - it still pulled in a lot of nulls where there is definitely values.

 

But, I persevered and finally found a work around.

 

My labor table is not tied directly to the job detail table - there's a couple more layers in between.  In a perfect world, this would be a single table and I would be able to create a hierarchy, but sadly the data source I have to work has it all divided into separate tables.  So it goes job detail to department (as 1:*), then dept to task (also as 1:*), then task to labor (also as 1:*).  Ultimately it still flows through the whole series of four tables as a continuous 1:* relationship.

 

But those couple extra layers seem to be the problem.  I tried creating a duplicate of the top level job detail table and reduced it to just the key column and the couple extra columns I need, then created an active relationship directly to the labor table (with an inactive relationship to the original high level job detail table).  Now when I pull in the required SERIAL column from the extra little job detail helper table, I don't get blanks anymore.

Syndicated - Outbound

Hello @CalanFCfx Congradulations on self solving your issue.

 

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)