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,

 

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