Forum Discussion
Excluding specific rows based on content
- 5 years ago
Issue 1:
EXCEPT ( <LeftTable>, <RightTable> )it excludes from LeftTable the rows also existing in RightTable; which means all columns of a row, i.e. VPT, DATE, EMP NAME and AMOUNT in your case, are taken into consideration. At a glimpse, for row 'Travel Claims Log'[VPT #]=61854, 'Travel Claims Log'[AMOUNT]=477.45 doesn't exist in 'RStars Travel', thus this row is kept.
Issue 2,
RStars Travel = SELECTCOLUMNS( FILTER( RStarsTravelReport, NOT ( LEFT( RStarsTravelReport[RSTravel_VPTNbr] ) = "C" || LEFT( RStarsTravelReport[RSTravel_VPTNbr], 2 ) = "LT" ) ), "VPT #", RStarsTravelReport[RSTravel_VPTNbr], "Date", RStarsTravelReport[Final_Approv_Dt], "Employee Name", RStarsTravelReport[Vendor_Name], "Amount", MAX( RStarsTravelReport[Sfms_Trans_Amt], 0 ) )filter out undesired rows with specific symbols upon creating the new table.
Issue 1:
EXCEPT ( <LeftTable>, <RightTable> )
it excludes from LeftTable the rows also existing in RightTable; which means all columns of a row, i.e. VPT, DATE, EMP NAME and AMOUNT in your case, are taken into consideration. At a glimpse, for row 'Travel Claims Log'[VPT #]=61854, 'Travel Claims Log'[AMOUNT]=477.45 doesn't exist in 'RStars Travel', thus this row is kept.
Issue 2,
RStars Travel =
SELECTCOLUMNS(
FILTER(
RStarsTravelReport,
NOT (
LEFT( RStarsTravelReport[RSTravel_VPTNbr] ) = "C"
|| LEFT( RStarsTravelReport[RSTravel_VPTNbr], 2 ) = "LT"
)
),
"VPT #", RStarsTravelReport[RSTravel_VPTNbr],
"Date", RStarsTravelReport[Final_Approv_Dt],
"Employee Name", RStarsTravelReport[Vendor_Name],
"Amount", MAX( RStarsTravelReport[Sfms_Trans_Amt], 0 )
)
filter out undesired rows with specific symbols upon creating the new table.