Forum Discussion

robarivas's avatar
robarivas
Post Patron
6 years ago
Solved

Power Query convert text to date

Pulling many millions of rows from dozens of csv files. Some of the files have a date column coming in to power query in a serial format while others have that same column coming in the mm/dd/yyyy format. Although Power Query initially "sees" the column in the "Any" format, you can tell the entire column is inherently text. So when I change the column data type to Date the mm/dd/yyyy rows convert successfully but the serial format cells do not. If I change those serial format cells to number type first then the date conversion works but that then causes the mm/dd/yyyy cells to fail. So I sort of have a mixed format situation. I can overcome this with the try...otherwise pattern but that seemed to bring my query to a crawl, which is a major problem when dealing with so many millions of rows. Any ideas how I can efficiently get the mixed format column successfully converted to date?

Need Help
  • robarivas's avatar
    robarivas
    6 years ago

    Thanks stretcharm and AlexAlberga727 

     

    Here is what I ended up trying and it worked, at least for my particular situtation. The key is to use Value.FromText wrapped around Text.Trim

     

    = Table.TransformColumns(PreviousStepOrOtherQuery,{{"AMOUNT", each Currency.From(Value.FromText(Text.Trim(_))), Currency.Type}, {"QUANTITY", each Int64.From(Value.FromText(Text.Trim(_))), Int64.Type}, {"ORDER_DATE", each DateTime.From(Value.FromText(Text.Trim(_))), type datetime}, {"SALE_DATE", each Date.From(Value.FromText(Text.Trim(_))), type date}})

     

    I tried getting rid of the each and underscore pieces of the code above but that didn't want to work. However, the M code above works just fine.

4 Replies

  • AlexAlberga727's avatar
    AlexAlberga727
    Resolver II
    6 years ago

    I would interested in a solution to your issue. I believe a lot of the data manipulation will need to be done within the power query editor before pulling the records in.

    Could you provide some examples of the data your're attempting to reformat?

  • stretcharm's avatar
    stretcharm
    Memorable Member
    6 years ago

    I would add a column using an expression like this that checks if the conversion works.

    if Value.Is(Value.FromText([DateValue]), type datetime) then [DateValue] else if 
Value.Is(Value.FromText([DateValue]), type date) then [DateValue] 
else null

    If there are lots of different values it might be easier to have a column for each type, you could then replace errors with null. Add a final column to pick the non null date.

     

    Adding column using examples can help get build powerquery expressions. This video shows how to make a date from other columns.

    https://www.youtube.com/watch?v=t1WLdABqZeQ

     

     

     

    • robarivas's avatar
      robarivas
      Post Patron
      6 years ago

      Thanks stretcharm and AlexAlberga727 

       

      Here is what I ended up trying and it worked, at least for my particular situtation. The key is to use Value.FromText wrapped around Text.Trim

       

      = Table.TransformColumns(PreviousStepOrOtherQuery,{{"AMOUNT", each Currency.From(Value.FromText(Text.Trim(_))), Currency.Type}, {"QUANTITY", each Int64.From(Value.FromText(Text.Trim(_))), Int64.Type}, {"ORDER_DATE", each DateTime.From(Value.FromText(Text.Trim(_))), type datetime}, {"SALE_DATE", each Date.From(Value.FromText(Text.Trim(_))), type date}})

       

      I tried getting rid of the each and underscore pieces of the code above but that didn't want to work. However, the M code above works just fine.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Function for Power query that converts a text with dates to dates based on a kwon format.

    (Creating a function from an existing endpoint is a simple process. Right-click on the endpoint name and choose Create Function.)

    Over the new function, in advace editor past the code:

    let
    Source = (dateColumn as text,DateSchema as text) => let
    MyDelimiter = Text.Range(DateSchema,Text.PositionOfAny(DateSchema,{"/","-",".","\","_"}),1),
    DateList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(dateColumn),
    SchemaList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(DateSchema),
    TableDate = Table.FromColumns({DateList,SchemaList}),
    myDay = "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "D" )[Column1]{0}),
    myDD = Text.Range(myDay,Text.Length(myDay)-2,2),
    MyMonth= "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "M" )[Column1]{0}),
    myMM = Text.Range(MyMonth,Text.Length(MyMonth)-2,2),
    myYear= "20" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "Y" )[Column1]{0}),
    myYYYY = Text.Range(myYear,Text.Length(myYear)-4,4),
    DateByParts = myYYYY & "-" & myMM & "-" & myDD,
    DateCleaned = Date.FromText(DateByParts)
    in
    DateCleaned
    in
    Source

    Then invoke the function in a new column.