Forum Discussion

cosminc's avatar
cosminc
Post Partisan
8 years ago
Solved

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           thanks in advance! Cosmin
Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    8 years ago

    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.

     

cosminc's avatar
cosminc
Post Partisan
8 years ago

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

 

 

 

ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
8 years ago

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.

  • cosminc's avatar
    cosminc
    Post Partisan
    8 years ago

    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