Forum Discussion
Power Bi to Find Missing Projects
I am using two sheets for information (Separate Data Sources). The purpose of the report would be to search both sheets (Master Sheet Vs Log). The Master sheet against the other Log sheet to determine what Project#'s (Column) are on the Main sheet but not on the other and then produce the list of the Project#'s (Column that are not on the Log Sheet. Any help is greatly appreciated. Thanks
Hwagner
Hi Hwagner ，
We can achieve this in two different ways:
1. In the Power Query: as AlexisOlson suggested, using Merge fuction with the Join Kind type selected as Left (Right)-Anti, you can also refer to the links below.
Find Mismatch Rows with Power Query in Power BI
Finding non-matching values between two tables in Power BI
2. Use DAX to implement: I created a sample pbix file(see attachment), please check if it is what you want. Create the following measure to determine if each Project# in master is also displayed in the log sheet. Then apply the measure to the Filters pane to filter the visual.
Flag = VAR _tab = EXCEPT ( VALUES ( 'Master'[Project#] ), VALUES ( 'Log'[Project#] ) ) RETURN IF ( SELECTEDVALUE ( 'Master'[Project#] ) IN _tab, 1, 0 )You can also refer the following link to create a calculated table to get the Project# list...
Get Records from Table A that are not in Table B
Is there anything else you need help with regarding this post? And if not, could you please mark it as Answered? This will also help others in the community to find this solution easily if they are having the same problem as you. Thank you.
Best Regards
3 Replies
I'd recommend trying an anti-join:
https://docs.microsoft.com/en-us/power-query/merge-queries-left-antiAlexisOlson Thank you, this seems to have worked but running a beta test now. I appreciate the help.
Hi Hwagner ，
We can achieve this in two different ways:
1. In the Power Query: as AlexisOlson suggested, using Merge fuction with the Join Kind type selected as Left (Right)-Anti, you can also refer to the links below.
Find Mismatch Rows with Power Query in Power BI
Finding non-matching values between two tables in Power BI
2. Use DAX to implement: I created a sample pbix file(see attachment), please check if it is what you want. Create the following measure to determine if each Project# in master is also displayed in the log sheet. Then apply the measure to the Filters pane to filter the visual.
Flag = VAR _tab = EXCEPT ( VALUES ( 'Master'[Project#] ), VALUES ( 'Log'[Project#] ) ) RETURN IF ( SELECTEDVALUE ( 'Master'[Project#] ) IN _tab, 1, 0 )You can also refer the following link to create a calculated table to get the Project# list...
Get Records from Table A that are not in Table B
Is there anything else you need help with regarding this post? And if not, could you please mark it as Answered? This will also help others in the community to find this solution easily if they are having the same problem as you. Thank you.
Best Regards
