Looks like a preceding load won't work with CrossTable(). No matter - try this:
LOAD * FROM
(ooxml, embedded labels, table is Sheet1);
Date#(PayDate,'DD/MM/YYYY') AS PayDate,
WHERE PayDate <> 'Total'
DROP TABLE Data_temp;
That worked perfectly.
Thanks for your help.
If I could ask you a related question:
Sometimes there are other (non-date) column to the right of the date columns.
Is there a way to exclude any such columns (in the WHERE statement?).
Perhaps exclue on the basis that they are text rather than date?
Anyway, thanks again for your help so far.
That didn't work. No records load into the Data table.
I suspect that it may be due to the fact that PayDate iis a text field, which is only translated by the Date# during the Load?
Maybe there is a way to delete text values for the PayDate field after the load using something like WHERE Not IsText?
Anyway, you have done more than enough, so I will understand if you are too busy to persist with this.