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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.