Forum Discussion
Please help! Data > Get Data (from File) is driving me insane
ChrizK ISBLANK() requires the cell to contain absolutely nothing. I suspect that your transformation to Date and then to Text returns something to the cell that just isn't visible. Try this in stead:
=IF(cell="","PASS","FAIL")On the other hand, you say that you are "forced to used Get Data". I would want to suggest to do all your validations with "Get and Transform Data" (a.k.a. Power Query), in stead of working with all sorts of formulae in Excel. Power Query is extremely powerful in formatting, matching and more. But since I haven't seen your data, I may be mistaken.
ChrizK ISBLANK() requires the cell to contain absolutely nothing. I suspect that your transformation to Date and then to Text returns something to the cell that just isn't visible. Try this in stead:
=IF(cell="","PASS","FAIL")
On the other hand, you say that you are "forced to used Get Data". I would want to suggest to do all your validations with "Get and Transform Data" (a.k.a. Power Query), in stead of working with all sorts of formulae in Excel. Power Query is extremely powerful in formatting, matching and more. But since I haven't seen your data, I may be mistaken.
NB I am only transforming the date column, however, if I view the columns in the Transform operation, I can see that each column with no content is identified as Text (not sure why it is General at the point it is loaded).
Thank you for your quick reply ...I will try "" 🙂
ChrizK ....... provided you're not a Mac. If so, PQ will not (yet) work for you.
ChrizK Okay! Thought you were referring to PQ when you wrote about "Get Data" and "transforming".
The attached link would be a good starting point to learn about PQ.
https://exceloffthegrid.com/power-query-introduction/
Good luck!
Thank you. As you would expect, your suggestion works perfectly ...the simplest solution is often the best.
Not knowing that Get Data actually is Power Query (on PC).
https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536aI am however at a bit of a loss as to how PQ can be used for validation.
Perhaps this is just a basic understanding of what I mean by validation.
As an example, I want to validate 'field one' is the correct date format.
I use =IF(COUNTIF(C2,B2),"PASS","FAIL"), with my expect result being ????-??-??
This results in PASS which I can easily see, especially with conditional formatting.As PQ appears to be Extraction>Transform>Load, how could I validate the content of fields within a CSV?
ChrizK Can't tell unless you disclose what you are validating. The picture you uploaded doesn't mean much to me. My gut feeling was that connecting to a CSV and then do some clever steps in PQ could do much of the validation. But, again, perhaps I was wrong.