Forum Discussion

SandeA's avatar
SandeA
Helper III
5 years ago
Solved

Excluding specific rows based on content

I'm sorry in advance if there is an easy solution that I'm just not finding. I'm working on a very high priority Dashboard project and have spent 2 days trying to figure out how to make this work. I need to have something to show them by the end of the week.

 

I have two issues that I just can't figure out.

 

ISSUE 1:

I created a table to only have specific columns from an imported sql table. This is so I can use this in an EXCEPT table later:

 RStars Travel = SELECTCOLUMNS(RStarsTravelReport,
"VPT #", RStarsTravelReport[RSTravel_VPTNbr],
"Date",RStarsTravelReport[Final_Approv_Dt],
"Employee Name",RStarsTravelReport[Vendor_Name], "Amount",IF(RStarsTravelReport[Sfms_Trans_Amt]>0,RStarsTravelReport[Sfms_Trans_Amt],0))
 
Here is a sample of that table:

The other table (Travel Claims Log) I used in the EXCEPT table has this data: (note: 61853 is the first record and lowest number in this table)

 

Then I created the EXCEPT table:
OutstandingTravel = EXCEPT('Travel Claims Log','RStars Travel')
 
This is a sample of that table:
 

 

The table genrated just fine, however it pulled in rows that were in both of the tables. I tried switching the order of the table and that definitely isn't what I'm looking for! I need to see all records that are in the Travel Claims Log but NOT in the RStars Travel. While it did pull the first record correctly, it should not have pulled the remaining items because those are in both tables. Is it because the Travel Claims has each VPT # summarized and the RStars Travel does not? If so, is there a way around that?
 
ISSUE 2
Some of the VPT # records in the RStars Travel table begin with a 'C' or 'LT'. We do not want to include these rows in any of our dashboard visuals or counts. Is there a way to exclude those when I'm creating the table? I couldn't find a function or operator that was for 'does not include' that would work in this situation. 
 
Thank you for any help you can give, you all are GREAT!!!
Sande

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    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.

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    SandeA 

    When you use EXCEPT, it matched columns from both tables by positions, and columns are compared 
    "If a row appears at all in both tables, it and its duplicates are not present in the result set. If a row appears in only table_expression1, it and its duplicates will appear in the result set."

    Regarding the Issues # 2 yes it is possible. 

    Can you share a PBIX file with sample data with the scenario and the expected results?

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    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.