Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
cosminc
Post Partisan
Post Partisan

make date from no

Hi all,

i have an issue:

how can i create date from these:

where 112018 = 1st jan 2018

6152018 = 15 jun 2018

11112018 = 11 nov 2017

 

 

 

mdy.jpg

 

 

thanks in advance!

Cosmin

1 ACCEPTED SOLUTION
ChrisMendoza
Resident Rockstar
Resident Rockstar

@cosminc,

 

This is not elegant and I'm hoping there is another solution that is...

 

Using:

date
112018
6152018
11112018

 

In the Query Editor ( Add Column > Custom Column) ,

 

 

YearNumTEXT = Text.End ( Number.ToText ( [date] ) , 4 )

 

DayNumTEXT = 

if Text.Length ( Number.ToText ( [date] ) ) = 6

then Text.Middle ( Number.ToText ( [date] ), 1, 1)

else if Text.Length ( Number.ToText ( [date] ) ) = 7

then Text.Middle ( Number.ToText ( [date] ), 1, 2)

else Text.Middle ( Number.ToText ( [date] ), 2, 2)
MonthNumTEXT =

if Text.Length ( Number.ToText ( [date] ) ) = 6

then Text.Start ( Number.ToText ( [date] ), 1)

else if Text.Length ( Number.ToText ( [date] ) ) = 7

then Text.Start ( Number.ToText ( [date] ), 1)

else Text.Start ( Number.ToText ( [date] ),2 )
DateFromText = 

Date.FromText ( Text.Combine ( { [MonthNumTEXT], [DayNumTEXT], [YearNumTEXT] },"/") )

That should allow you to format as Data Type Date.

 

3.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

6 REPLIES 6
ChrisMendoza
Resident Rockstar
Resident Rockstar

@cosminc,

 

This is not elegant and I'm hoping there is another solution that is...

 

Using:

date
112018
6152018
11112018

 

In the Query Editor ( Add Column > Custom Column) ,

 

 

YearNumTEXT = Text.End ( Number.ToText ( [date] ) , 4 )

 

DayNumTEXT = 

if Text.Length ( Number.ToText ( [date] ) ) = 6

then Text.Middle ( Number.ToText ( [date] ), 1, 1)

else if Text.Length ( Number.ToText ( [date] ) ) = 7

then Text.Middle ( Number.ToText ( [date] ), 1, 2)

else Text.Middle ( Number.ToText ( [date] ), 2, 2)
MonthNumTEXT =

if Text.Length ( Number.ToText ( [date] ) ) = 6

then Text.Start ( Number.ToText ( [date] ), 1)

else if Text.Length ( Number.ToText ( [date] ) ) = 7

then Text.Start ( Number.ToText ( [date] ), 1)

else Text.Start ( Number.ToText ( [date] ),2 )
DateFromText = 

Date.FromText ( Text.Combine ( { [MonthNumTEXT], [DayNumTEXT], [YearNumTEXT] },"/") )

That should allow you to format as Data Type Date.

 

3.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Hi Chris,

very helpful and we're close, thanks a lot!

there are only 2 situation to solve:

1. when we have for example 12112018 (len=8)

2. 1112018 can be 11 jan and also 1 nov or  1212018 - can be 1 dec or 21 jan; in addition to this and i think very useful is that i obtained another column in which i had a date close to the date which we have to extract (not necessarily the same)

ex

1112018      1/13/2018

1212018       11/30/2018

 

Can you please digg on this please?

Thanks,

Cosmin

 

 

 

@cosminc,

 

My demonstration should help you break the date into pieces so you can properly make into an actual date value.

 

If your data is in such a way that sometime 'month' comes before 'day' or vice versa that would be a very special case. How do you know that is the case by the way? I do not know of a way to find these ambigious cases.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Hi

It's fantastic your demonstration

further i doubled these columns which you created, i create a column with difference between the date from the 1st and the date available (which is close +-7days) and after that i create conditional column when the difference is <-7 & >+7, the final date will be the second option (date created by you v2) else the first date created by you (v1)

you helped me very very much!

Thanks!

Cosmin

dedelman_clng
Community Champion
Community Champion

Any chance you can get the source data cleaned up to be a standard 8 digit MMDDYYYY ?  As it currently sits I don't see a way to parse the number into month, day and year.

 

For example: 1122018 - would this be 12 Jan 2018 or 2 Nov 2018 ?

Hi,

no but in addition i have a column with a date close to that:

1112018      1/13/2018

1212018       11/30/2018

Thanks,

Cosmin

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.