Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
Hey @Anonymous - I'm not convinced the join has anything to do with it. Here is what could have happened:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingNever 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:
Then begin your real transformations.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry, 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYeah, 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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks, it turns out there was a single rogue entry in the supplier number column. All sorted now!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting