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.

     

ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
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