LEFT OUTER JOIN not working

[jppuam]

Hello,

i've a table A with the follow:

code_ID    department     costs

1               chemistry        100

2               math                200

3               sociology         300

4               reading            400

and a table B with:

code_ID    department     costs

1               chemistry        100

3               sociology         300

i want to have a calculatedtable that gives me the data in table A, thats not in table B ?

its the same that i exclude the data in table B that exist in table A.

code_ID    department     costs

2               math                200

4               reading            400

I've try several functions, but i must be doing some wrong.

thanks

JP

[Jos_Woolley · Accepted solution]

If the matching is to be based on all 3 columns:

NewTable =
EXCEPT ( Table1, Table2 )

Regards

[jppuam]

i try that, but i was using a calculated table that i've made and it wasnt working.

I've change to the original table, an it works....dont understand what was the problem, but its working.

thanks

Jos_Woolley

[Jos_Woolley]

Hi,

New query, merge as new:

You can then delete the final column resulting from this query.

If the operation is to consider not just the code_ID column then select all columns as required when performing the merge, for example:

Regards

Regards

[jppuam]

Jos,

when i try to merge two query by code, ir gives me this error:

Expression.Error: The 'count' argument is out of range. What is the problem ?

thanks,

JP

[Jos_Woolley]

Hi JP,

Most likely because you have some rows containing errors prior to the merge. Difficult to be sure without seeing the actual data though, I'm afraid.

Regards

[jppuam]

Maybe its because of the size ? I'm filterinig 1,8M lines ? 

The columns dont have any erros, can i do it by DAX ?

thanks,

JP

[Jos_Woolley]

Sorry, I'm afraid I don't know the answer as to why you're getting that error.

Regards

[jppuam]

I was trying to do it with DAX, i'll try it like you said.

JP

[Fowmy]

@jppuam 

Use Left-Anti Join when merging the tables in Power Query

https://docs.microsoft.com/en-us/power-query/merge-queries-left-anti