Forming a Date column

[PrakashMathi]

I want to form a date column for a calendar year.

<Dimension 1>

2018

Jan

1

2

3

.

.

31

Feb

1

2

.

Dec

1

.

.

31

2019

Jan

1

.

etc

I want to form a date using Dimension 1 column.

Expected output = 2018-1-30

Attached data set

Message was edited by: prakash mathiazhagan

[Anil_Babu_Samineni]

Not sure, I understand the problem. Can you bell on that exactly what you need?

[shiveshsingh]

Do you need a calendar?

[PrakashMathi]

I want a date column

[PrakashMathi]

I want a date column in the presentation layer in the format of 2018-1-1, 2018-1-2,etc

[shiveshsingh]

May be this

let vMin = num(Today());

let vMax = num(Today()+30);

Calendar:

load date($(vMin)+RowNo()-1) as Date

AutoGenerate($(vMax)-$(vMin));

[jonathandienst]

How should he table containing the date column be associated with the rest of the data model?

[PrakashMathi]

it is the single table. There is not any other table in data model

[PrakashMathi]

Actually i was thinking of creating another column which will have Jan/Feb/Mar like that and then to concatenate.

[PrakashMathi · Accepted solution]

All,

Thanks for your help.

Got the answer. Below code works and provides answer to my solution.

Table:
LOAD Date,
If(Date > 2000 and IsNum(Date), Date, Peek('Year')) as Year,
If(IsText(Date), Date, Peek('Month')) as Month,
MakeDate(If(Date > 2000 and IsNum(Date), Date, Peek('Year')), Month(Date#(If(IsText(Date), Date, Peek('Month')), 'MMM')), Date) as New_Date
FROM

(ooxml, embedded labels, table is Sheet1);

FinalTable:
NoConcatenate
LOAD *
Resident Table
Where Len(Trim(New_Date)) > 0;

DROP Table Table;

EXIT SCRIPT;