Log In
Register
Home
October 7, 2021
Offline
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.
Trusted Members
Moderators
November 1, 2018
Offline
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.
Moderators
January 31, 2022
Offline
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.
October 7, 2021
Offline
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.
Moderators
January 31, 2022
Offline
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.
October 7, 2021
Offline
In PQ the column is set as Number, so I don't know where the problem is.
Moderators
January 31, 2022
Offline
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.
Trusted Members
Moderators
November 1, 2018
Offline
The Plot column in your Table2023 query is set to Any, not number.
October 7, 2021
Offline
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.
July 16, 2010
Offline
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
1 Guest(s)
