Forum Discussion
Issue with Left Anti Join - Type Text
Hey Anonymous - I'm not convinced the join has anything to do with it. Here is what could have happened:
- You are converting some field to numerical of some sort (whole number, currency, percentage, whatever)
- Way WAY below the 1,000 records Power Query uses for caching is a text value in that field.
- When you do the merge and expand the table, now that record that might be record 30,000 is now record number 200. Now Power Query will try to convert it to a number and it breaks.
So look carefully at all numeric fields.
A quick way to do this is to load both tables into Power BI as a test. That will load all records. Power BI will give you a good clean error on the numeric field with an alpha in it.
Or, you can try to use the Table.Profile() function on each table. Just wrap the last line of each table with Table.Profile() in the formula bar and wait. See if anything odd shows up.
Sorry, the format of the columns which I am match is text (ABC), my AB123 example was an example value from the column. I am only using the columns which I need, and the left outer join works perfectly fine.
I've referenced the 'exclusion' table, kept only the column with the key and am using that to match. If I choose, left inner, it works fine, if I choose left anti it fails with the data format error.
I just don't know why the same column result in an error with a left anti join but works with a left inner join.
And you are 100% sure both columns are text/ABC Anonymous ? I would have to see the query or data to understand. The M code might help, but depends on the source data. I do all sorts of joins with text and have never had Power Query try to convert anything to a number, but I am very careful to always set my data types first.
If you want to share the data with me privately, you can PM me via this forum's board vs posting a live dropbox/onedrive link here.
Yeah, it's weird. I don't know why one join type would work and the other fail with the exact same columns used.
Here's the exclusion column (note there are records in there that are alpha numeric despite only seeing numeric values in the screen shot):
And the matching column from the other table:
Both are cleaned and trimmed.
Result with left Anti:
Result with Left Outer:
Hey Anonymous - I'm not convinced the join has anything to do with it. Here is what could have happened:
- You are converting some field to numerical of some sort (whole number, currency, percentage, whatever)
- Way WAY below the 1,000 records Power Query uses for caching is a text value in that field.
- When you do the merge and expand the table, now that record that might be record 30,000 is now record number 200. Now Power Query will try to convert it to a number and it breaks.
So look carefully at all numeric fields.
A quick way to do this is to load both tables into Power BI as a test. That will load all records. Power BI will give you a good clean error on the numeric field with an alpha in it.
Or, you can try to use the Table.Profile() function on each table. Just wrap the last line of each table with Table.Profile() in the formula bar and wait. See if anything odd shows up.
Thanks, it turns out there was a single rogue entry in the supplier number column. All sorted now!