Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date from text issue

Hey Guys, 

 

I have a set of data for training and I reach the point where I pivot the data properly yet, when I try to change the data type from text to date format (by changing type or using the date from text function it always returns error)

 

any Idea how to fix this ??

 

Tips and Tricks
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    Date.From(Text.Combine(Splitter.SplitTextByAnyDelimiter({"st ","nd ","rd ","th "})([Attribute]),"-"))

5 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    You need to remove the ordinal suffixes.

    Something like:

        #"Convert to Date" = Table.TransformColumns(#"Previous Step", {
        "Attribute", each Date.From(List.Min(List.Accumulate({"st","nd","rd","th"},{}, (state, current)=>
            state & {Text.Replace(_,current,"")}))), type date
    })
  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    Date.From(Text.Combine(Splitter.SplitTextByAnyDelimiter({"st ","nd ","rd ","th "})([Attribute]),"-"))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Worked like magic
      don't wanna bother, but can u tell me what was I doing wrong and how u wrote this formula, I'm a newbie in M

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        3 years ago

        text with "st ","nd ","rd ","th " can not be recognized as a date,  so you must replace them with "-".

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, Anonymous ;

    You could try it.

    1.replace "," to ""

    2.add custom column.

    Date.FromText(
Text.Middle(Text.SplitAny([Hours], " "){0},0,
    Text.Length(Text.SplitAny([Hours], " "){0})-2)
    & "-"&Text.SplitAny([Hours], " "){1}  
    & "-"&Text.SplitAny([Hours], " "){2})

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.