CrossTable and dates

[Anonymous]

Hi all,

I have a problem with crossTable Load. I have this table in Excel:

I use this code:

Capacity:

CrossTable (Months,Utilization)

LOAD

    Name as WorkerName,

    "43009",

    "43040",

    "43070",

FROM [path.xlsx]

(ooxml,embedded labels,  table is Sheet1);

I need to view dates, but when I try to convert it to date, no results (Date, Timestamp...)

How to load this data? Especially for a heatmap?

Thaks a much for your ideas

[shraddha_g · Accepted solution]

Try:

Capacity:

CrossTable (Months,Utilization,1)

LOAD

    Name as WorkerName,

    "43009",

    "43040",

    "43070",

FROM [path.xlsx]

(ooxml,embedded labels,  table is Sheet1);

Final:

Load WorkerName,

          Utilisation,

date(num#(purgeChar(Months,'"'),'00000')) as Months

Resident Capacity;

Drop table Capacity;

[Anonymous]

Thank you very much

[mboreham]

Thank you so much for this. I will never get the last four hours of my life back but I won't waste the next four. This is wonderful

[abhijith28]

I have a similar requirement where date format is Jun-19, jul-19

But i am not able to see the data after loading.

Please help me with this issue