Excluding rows based on combinations in another table

[mira_bella]

Hello,

I have a table with financial transactions: basically Account, Cost_center, and Amount.

I need to exclude the rows with certain combinations of Account + Cost_center. Since there are quite many, and there's no sensible logic in their numbering/naming, I decided to put the list of exceptions in another table, which would be just two columns: Account and Cost_center. (both relate as many to many, so the same Account is many times in the list and the same Cost center is many times in the list, but each combination is unique)

Then I need a function, which would look for matching combination in "Exceptions" for each row of my "Transactions" table. If it matches, the row should be excluded (or maybe just marked as "excluded" in additional column, which I then can use as filter) 

I'm new to PowerBI and "learning by doing" on my own, so I don't quite know how to do this in a better way. 

Adding a new query which will compare the tables and exclude rows?  

Or adding a searching function somewhere, which I then call in my calculation of a new column? somth like

IF exception_search=true then excluded_row=Yes.

But where and how to write such function/query? I'm not quite familiar with the syntax in PowerBI. I would be thankful for advice and code examples, or maybe just recommend me a link where to read..

[MFelix · Accepted solution]

Hi @mira_bella,

Taking into account that you already did the auxiliary table yo need to do the following:

1 - Create a custom column in the Exceptions table with Account + Cost_center = ID.

2 - Create a custom column in the Financial transactions with Account + Cost_center = ID

With this two columns you can now make a merge between the two tables (vlookup in excel)

3 - In the Financial transaction choose option to Merge queries and be sure to select the Full outer (all rows from both)

4 - Chose expand on the column just created and select the ID then

5 - Filter out all that are not null.

[MFelix · Accepted solution]

Hi @mira_bella,

Taking into account that you already did the auxiliary table yo need to do the following:

1 - Create a custom column in the Exceptions table with Account + Cost_center = ID.

2 - Create a custom column in the Financial transactions with Account + Cost_center = ID

With this two columns you can now make a merge between the two tables (vlookup in excel)

3 - In the Financial transaction choose option to Merge queries and be sure to select the Full outer (all rows from both)

4 - Chose expand on the column just created and select the ID then

5 - Filter out all that are not null.

[mira_bella · Accepted solution]

Thank you @MFelix! I managed to find the solution without combining columns. I did the "Merge query" selecting both Account and Cost center as matching columns in both tables, and setting the "Left Anti" join type (all rows from  Transactions except those which are also in Exceptions). Seems like it works correctly, comparing the final sums with our main reporting system.

Well that turned out to be easy, just as a newbie I couldn't figure out where to look for this feature 🙂 Hopefully helps someone else.

[v-yuezhe-msft]

Hi @mira_bella,

Glad to hear the issue is solved. You can accept helpful replies as solution, this way, other community members would easily find the solution when they get same issues.

Besides, you can get more familiar with features in Power BI Desktop by reviewing different topics in the following link.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-get-the-desktop/

Thanks,
Lydia Zhang