Hi,
In the attached file you can see that my FILTER formula in column H doesn't find anything because the values in "AllYears" sheet column B is not the same as the values in "2023" column E.
If I copy the value from "2023" to "AllYears", like I did in row 2, it will work OK, but I want to know what is the format issue here.
I tried to set both values to Number and Text, but it didn't make any difference.
Regards,
Ayal Telem.
The values in Table2023 are text, not numbers. You could amend your formula to use Table2023[Plot]+0=[@Plot] as the criterion. The +0 will coerce the text numbers to real numbers. Note: you will need to amend the value that you copied into your AllYears table back to a real number.
The Plot column in Table2023 contains text that all look like the number 1. That's why the formula breaks. I notice you used Power Query to import data. Make sure that the data types are correctly set.
Ooops, waited too long to send my reply.
Thank you very much Velouria & Riny.
I knew it has to do with different formating between the cells, but couldn't, and still doesn't understand where the difference comes from, and how can I fix it in the cell's level rather than the Formula level.
When I do the Ctrl+1 (Format Cell) I see that both are formated the same, so where else the cell's properties is written and determined?
Regards,
Ayal Telem.
Formatting a text "1" as a number does NOT change it into a real number. Make sure that you have set the correct data type in PQ for the Plot column and reload the table to Excel That should fix it.
In PQ the column is set as Number, so I don't know where the problem is.
That's odd. Since Column E in the 2023 tab is formatted as Number, then loading the table from PQ where the Plot column data type is 'Whole Number' should not make it text in the 2023 tab. But as said earlier, all the ones in column E are texts.
The Plot column in your Table2023 query is set to Any, not number.
I changed it to Numers both through the Ctrl+1, and PQ, but the problem persists.
Anyway, i think we'll leave it as one of life's mysteries.
Hi Ayal,
I edited the query and changed the data type for column E to whole number (as Velouria said, it's currently formatted to 123ABC which is data type 'Any', which is strictly not a number), and it correctly loads to Excel as a number.
Mynda
Hi, Mynda,
The file I attached is a sample of the original, as the file is in Hebrew.
I tried to change the column type in PQ, but it doesn't fix the issue.
Thanks anyway,
Ayal Telem.
