cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SandeA
Helper I
Helper I

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:
SandeA_0-1624466748930.png

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)

SandeA_1-1624466857905.png

 

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

 

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

 

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

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.

View solution in original post

2 REPLIES 2
CNENFRNL
Super User III
Super User III

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.

View solution in original post

Fowmy
Super User IV
Super User IV

@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?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors