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.
Never use the ABC123 format. That is not alphanumeric, but "Any" which means anything goes. It could be text, numeric, dates, boolean, even binary. Convert it to Text (ABC) first, then do the join. There is some data in there that Power Query is trying to convert to a number, and it is failing. Leaving it as Any will allow that. Forcing it to Text will prevent PQ from trying any further conversions.
The first thing you should do when importing data is:
- Generally get rid of any columns you don't need
- make sure all of the remaining columns have the data types properly set, never leaving Any (ABC/123) columns.
Then begin your real transformations.
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.