Splitting column and convert data

[Rkelly]

Hi,

I have a data table that records the date of a particular event through two seperate fields. One records the day of the week that an event is held the other is a 53 character field that nominates which weeks the event is occuring.

an example of the data is 

event_id - 591683

day_of_week - 1

Weeks - NNNYYYYYYYYYNNYYYYYYYYYYYNNYYYYYYYYYYNNYYYYYYYYYYNNNN

the result I am looking for is a column containing event id and a column containing the date of the event.

any assistance to transform the data would be appreciated.

Thanks 

[ImkeF]

This will return the weeknumbers in a list:

List.Transform(Text.PositionOf([Column1], "Y", Occurrence.All), each _+1)

You can use it in a custom column. When you expand it, you should have 3 columns that then can be further combined to a date (#date(YYYY, MM, DD))