Forum Discussion
Solved
Please help! Data > Get Data (from File) is driving me insane
I am forced to use Get Data to work around the insane situation with Excel reformatting dates (a centuries old problem that has been discussed to death and still no resolution since the dawn of time ...
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 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-5bfb1a6c536a
I 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.