Isuue with Datamodel with date formats

[abubakarsiddiq7]

hi experts,

I have a requirement on a data model with different date fields where my keys fields are having different data types

for example :

Table 1: ID is key,

Table 2:UID&DATE column

Table3: SID & DATE Column

Table 1:

Load*,

ID,YEARMONTH,SALES

from XYZ;

Load*,

UID,DATE,PLAN

from XYZ;

TABLE3:

SID,DATE,Profit

from XYZ;

i need to create the data model using the above three tables with the Master calendar (FY ) .Please help me

Sample Data is Attached

[anthonyj]

Hi @abubakarsiddiq7 ,

I wasn't sure if you wanted 3 tables associated through a common YearMonth column but this code will align the date formats based on the data you gave.

Tables 2 and 3 were showing up as day/month/year so your examples were all in January. This code swapped the days and months around.

[Table 1]:
LOAD
[ID],
date(date#([YearMonth],'YYYYMM')) as YearMonth,
[Sales]
FROM [lib://AttachedFiles/Datamodel.xlsx]
(ooxml, embedded labels, table is [Table 1]);

[Table2]:
LOAD
[UID],
date(date#(text(date(num([DATE]))),'MM/DD/YYYY')) AS [YearMonth],
[PLAN]
FROM [lib://AttachedFiles/Datamodel.xlsx]
(ooxml, embedded labels, table is Table2);

[Table3]:
LOAD
[sID],
date(date#(text(date(num([DATE]))),'MM/DD/YYYY')) AS [YearMonth],
[profit]
FROM [lib://AttachedFiles/Datamodel.xlsx]
(ooxml, embedded labels, table is Table3);

I hope this is of some assistance.

Thanks

Anthony