Issue with Left Anti Join - Type Text

[Anonymous]

fI've go two tables that I'm working with in Power Query. One contains invoice san data and the other includes invoice payment data. I'm trying to complete a left anti join so that I have a table that has the scan data that is not in the payment table by way of an invoice id. This ID is Alpha Numeric(AB123) so it has been set to text in Power Query. 

I can complete a let inner join fine matching on these two columns, but when I try to perform a left anti join I get a 'Data Format Error - We coundn't convert to number'. I'm not trying to convert anything to a number. Do left anti joins only work on numeric column types? I tried to find the requirements for the join type but didn't see any restriction on using a text formatte field. 

On the preview it appears to know what it's going to exlude:

It's just when applied that the error displays

[edhans]

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.

[Anonymous]

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. 

[edhans]

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.

[Anonymous]

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:

[edhans · Accepted solution]

Hey @Anonymous - I'm not convinced the join has anything to do with it. Here is what could have happened:

1. You are converting some field to numerical of some sort (whole number, currency, percentage, whatever)
2. Way WAY below the 1,000 records Power Query uses for caching is a text value in that field.
3. 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.

[Anonymous]

Thanks, it turns out there was a single rogue entry in the supplier number column. All sorted now! 

[edhans]

Great @Anonymous - glad you got it figured out. Sometimes weird issues can stay hidden until something else causes them to manifest. I've seen issues remain hidden after dozens of transformations, then a merge or group by will suddenly blow up and the cause was 10 steps earlier in the process.