Only show items with 'no data'

[JoeBrown]

I have 2 tables, one imported from local excel file, another from an imported dataset.

For the example of this help.

Excel Table 1

Item code 1	Product
SCAR123	Scarf
SHIR123	Shirt
JACK123	Jacket

Imported Table

Item Code 2	Product
SCAR123	Scarf
GLOV123	Gloves
SOCK123	Socks

I have created a relation in the tables on the Item Code 1 & 2. Created a table to show the Excel Table & a new column to show Item Code 2. Also, I clicked 'Show items with no data' to create the table

New Table

Item Code 1	Product	Item Code 2
SCAR123	Scarf	SCAR123
SHIR123	Shirt
JACK123	Jacket

I would like to show only those items where there is no data. How do I do this? The filter does not work when using the 'is empty' or 'is blank' as it leaves a fully empty table.

Ideal output

Item Code 1	Product	Item Code 2
SHIR123	Shirt
JACK123	Jacket

[mahenkj2]

Hi @JoeBrown 

Check this one pls:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

Hope it helps.

[JoeBrown]

Hi - Thanks. I have read through this, but these is no mention of how to show only those items with no data. Using my example 'New Table', the filter option for 'Item Code 2' shows the 1 option of 'SCAR123'. When I unselect this, is removes all the data in the table, and just the headers. There is no option to just show the blank/empty/missing data in 'Item Code 2' (and keep the columns Item Code 1 & Product for those with the missing Item Code 2)

[mahenkj2]

Hi @JoeBrown 

In that case, you can merge Excel Table 1 with Imported Table in Power query, and when you expand the columns from Imported Table, select join kind as below:

Then you get result as below:

This is one of the way. You can use Merge table as main table depending on your purpose.

Hope it helps.