Table visual missing random values

[CalanFCfx]

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.

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

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!

[mickey64]

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

[CalanFCfx]

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

[mickey64]

How about adding a column to the table?

[CalanFCfx · Accepted solution]

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.

[foodd]

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!