Results 1 to 3 of 3

Thread: PQ - Transforming Text to Date in Power Query

  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7

    Cool PQ - Transforming Text to Date in Power Query

    (question from the web)
    I ma trying to transfomr an 8 digit text to a Date in Power Query. For instance, I want to transform in Power Query 02012021 to 02/01/2021 and format it
    as a date?
    Text Text Date
    02012021 02012021
    02/01/2021

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Date = Table.AddColumn(Source, "Date", each 
            Date.FromText
            (
            Text.Format("#[day]/#[month]/#[year]", 
                [
                day = Text.Start([Text],2), 
                month = Text.Middle([Text],2,2), 
                year = Text.End([Text],4)
                ], 
            "en-GB")
            )
        ),
        Type = Table.TransformColumnTypes(Date,{{"Date", type date}})
    in
        Type
    Last edited by sandy666; 02-20-2021 at 05:00 PM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

  2. #2
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    94
    Rep Power
    12
    I don't know if there's any advantage with the following except for it being easy to change whether the source data is in US or UK date format:
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypewithLocale = Table.TransformColumnTypes(Table.TransformColumns(Source, {{"Text", each Text.Insert(Text.Insert(_,4,"/"),2,"/"), type text}}), {{"Text", type date}}, "en-US")
    in
        ChangedTypewithLocale
    by changing en-US to en-UK

    2021-02-21_183057.png
    Last edited by p45cal; 02-21-2021 at 11:39 PM.

  3. #3
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7

    Cool

    Quote Originally Posted by p45cal View Post
    by changing en-US to en-UK
    DataFormat.Error: The specified culture is not supported.
    Details:
    en-UK

    I suggest: en-GB
    Last edited by sandy666; 02-22-2021 at 05:10 AM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. PQ - How to get Total via Power Query
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 09-23-2020, 06:10 PM
  2. PQ - UnZip an use Content in Power Query
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 05-13-2020, 05:37 AM
  3. Extract Date and time from the Text Column
    By zorro in forum Excel Help
    Replies: 19
    Last Post: 09-17-2016, 10:16 AM
  4. Replies: 0
    Last Post: 01-12-2015, 01:09 PM
  5. Replies: 7
    Last Post: 03-11-2014, 05:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •