Forum Discussion
List missing information in new table
To determine if something is missing in the second table compared to the first table in Power BI:
Create Relationships: Establish a relationship between the two tables based on the common fields (e.g., Report Number and Item Number).
Create a New Table: Use a DAX formula like the following to identify missing items:
MissingItems =
EXCEPT(
SELECTCOLUMNS('FirstTable', "Report Number", 'FirstTable'[Report Number], "Item Number", 'FirstTable'[Item Number]),
SELECTCOLUMNS('SecondTable', "Report Number", 'SecondTable'[Report Number], "Item Number", 'SecondTable'[Item Number]))Visualize the Output: Use a table visualization to display the MissingItems table, showing the Report Numbers and Item Numbers missing from the second table.
This will directly list all missing combinations of report and item numbers.
Hello,rohit1991 ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hi,JinjiNoDie .I am glad to help you.
If your two tables are guaranteed to look up the same columns, then you can try creating a virtual table that contains all the data from the table by using the except function on both virtual tables
like this.
The EXCEPT function is mainly used to compare two tables and return the rows that exist in the first table but not in the second. Note that the two tables must have the same number of columns, and the order and data types of the columns must match.
Create two virtual tables, _table1 and _table2, containing the Report Number and Item Number columns in the FirstTable and SecondTable, respectively. Then, use the EXCEPT function to return the rows that are in _table1 but not in _table2.
This gives you the flexibility to perform data lookups on the original tables without affecting the structure and data of the original tables.
Below is my test data:
FirstTable
|
Report Number |
Item Number |
|
25-1 |
1001 |
|
25-1 |
1002 |
|
25-1 |
1004 |
|
25-2 |
1001 |
|
25-3 |
1002 |
|
25-3 |
1003 |
|
25-4 |
1101 |
|
25-4 |
1102 |
SecondTable
|
Report Number |
Item Number |
|
25-1 |
1001 |
|
25-4 |
1102 |
|
25-1 |
1004 |
|
25-2 |
1001 |
|
25-3 |
1002 |
|
25-3 |
1003 |
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.