Forum Discussion
Only show items with 'no data'
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 |
3 Replies
mahenkj2Solution Sage
Hi JoeBrown
Check this one pls:
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data
Hope it helps.
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.
