none
Change Text to date in PQ RRS feed

  • Question

  • Hi, I'm working in PQ with a large dataset (125000+ rows) that has been sourced for a number of years from various computer programs. Older data has dates in the format 20110817 ie year date month, but with nothing excel can recognise to tell it this is a date and not just a number. Newer dates further down the same column are in std date format.

    In excel I used to get around this using the following formula = if(cellref>60000,date(left(cellref,4),mid(cellref,2),right(cellref,2)),cellref) ie if the number in the reference cell is greater than 60000 return a date based on converting the text to a date, otherwise return the current cell value.

    I'm sure I must be able to apply the same logic in PQ, but I haven't figured out how. Any ideas?

    I'm using a custom column to try out ideas so far I can work out how to bring in the dates that are in the correct format as follows: 

    Table.AddColumn(#"Changed Type3", "Custom", each if [Manufacture Date] > 60000 then "1" else [Manufacture Date] )
    • Edited by Sue047 Monday, January 23, 2017 8:52 PM
    Monday, January 23, 2017 8:36 PM

Answers

  • Power Query is strict with regard to data types, unlike Excel. In other words: e.g. Power Query won't let you take the right 2 digits of a number, using a text function.

    Edit: assuming [Manufacture Date] is a number, my suggestion is to apply a "calculated split" (I just invented this terminology :-) ) and supply the year, month and day parts (in this sequence) to the #date function:

    Table.AddColumn(#"Changed Type3", "Custom", each if [Manufacture Date] > 60000

    then #date(

    Number.RoundDown([Manufacture Date]/10000,0),

    Number.Mod(Number.RoundDown([Manufacture Date]/100,0),100),

    Number.Mod([Manufacture Date],100))

    else Date.From([Manufacture Date]))



    Monday, January 23, 2017 9:29 PM
  • Monday, January 23, 2017 10:57 PM
  • If you prefer to modify the existing date column instead of creating a new one, you can try:

    let Source = .... ChangedType = Table.TransformColumnTypes(
    Source,
    {
    {"Manufacture Date", type text},
    ......
    }
    ), TransformedColumn = Table.TransformColumns( ChangedType, { "Manufacture Date", each if Text.Length(_) = 8 then Date.From(Text.Replace(_,Text.Middle(_,4,2),"/"&Text.Middle(_,4,2)&"/")) else Date.From(Number.FromText(_)), type date } ) in TransformedColumn


    Tuesday, January 24, 2017 2:00 AM

All replies

  • Power Query is strict with regard to data types, unlike Excel. In other words: e.g. Power Query won't let you take the right 2 digits of a number, using a text function.

    Edit: assuming [Manufacture Date] is a number, my suggestion is to apply a "calculated split" (I just invented this terminology :-) ) and supply the year, month and day parts (in this sequence) to the #date function:

    Table.AddColumn(#"Changed Type3", "Custom", each if [Manufacture Date] > 60000

    then #date(

    Number.RoundDown([Manufacture Date]/10000,0),

    Number.Mod(Number.RoundDown([Manufacture Date]/100,0),100),

    Number.Mod([Manufacture Date],100))

    else Date.From([Manufacture Date]))



    Monday, January 23, 2017 9:29 PM
  • Magic! Thanks Marcel.

    I played around a bit and also found another way that worked if I converted the Manufacture Date column to text...but will use the above as I think it's tidier.

    The other way found was as follows:

    = Table.AddColumn(#"Changed Type3", "AddManufacureDate", each if Text.Length([Manufacture Date])>6 then Date.FromText([Manufacture Date]) else[Manufacture Date])

    Monday, January 23, 2017 10:17 PM
  • Monday, January 23, 2017 10:57 PM
  • If you prefer to modify the existing date column instead of creating a new one, you can try:

    let Source = .... ChangedType = Table.TransformColumnTypes(
    Source,
    {
    {"Manufacture Date", type text},
    ......
    }
    ), TransformedColumn = Table.TransformColumns( ChangedType, { "Manufacture Date", each if Text.Length(_) = 8 then Date.From(Text.Replace(_,Text.Middle(_,4,2),"/"&Text.Middle(_,4,2)&"/")) else Date.From(Number.FromText(_)), type date } ) in TransformedColumn


    Tuesday, January 24, 2017 2:00 AM