Generate Dates based on Day names - Crosstable

[#1]

Hello.

I am looking to convert data into a linear table from crosstable structure and need help. My data looks like Sheet1 and my desired output is Sheet2 in the attached excel. Any help is appreciated.

Thanks.

Sheet1:

Sheet2:

[sunny_talwar]

May be this

MappingWeekDay:

Mapping

LOAD * Inline [

  WeekDay, WeekDayNum

  Sunday, 0

    Monday, 1

    Tuesday, 2

    Wednesday, 3

    Thursday, 4

    Friday, 5

    Saturday, 6

];

Table:

CrossTable (WeekDay, Value, 5)

LOAD [Week starts on],

    Category,

    Name,

    Task,

    State,

    Sunday,

    Monday,

    Tuesday,

    Wednesday,

    Thursday,

    Friday,

    Saturday

FROM

[..\..\Downloads\Crosstable.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  ApplyMap('MappingWeekDay', WeekDay, Null()) as WeekDayNum,

  Date(RangeSum([Week starts on], ApplyMap('MappingWeekDay', WeekDay, Null()))) as Date

Resident Table

Where Value <> 0;

DROP Table Table;