Results 1 to 10 of 10

Thread: Converting date values stored as text to dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Converting date values stored as text to dates

    I am importing some .txt files that are all bills from a local power company in order to analyze each location of our companies energy usage. The dates when imported into power query are obviously not recognized as dates but as text. I need them as dates for the obvious reason of analyzing month over month data.

    I tried creating a custom column and entering this formula with no luck

    = Table.AddColumn(#"Expanded NewColumn", "Custom", each DATE(LEFT([Column8],4),MID([Column8],5,2),RIGHT([Column8],2)))

    the concatenate formula worked beautifully so I figured I could just toss this in as well. Any ideas on what I can do to get around this issue?

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    Hi Craig,

    The Power Query functions aren't the same as Excel.

    You could do it the same way using Number.FromText() and Text.Range()

    Or.

    You could split your Date column into 3 other columns (year, month, day) and then use a custom column to create the date.

    Here's an example:

    Code:
    let
        Source = Table.FromRows({{"20160101"},{"20160201"},{"20160301"}},{"Dates"}),
        #"Split Column by Position" = Table.SplitColumn(Source,"Dates",Splitter.SplitTextByPositions({0, 4}, false),{"Year", "Other"}),
        #"Split Column by Position1" = Table.SplitColumn(#"Split Column by Position","Other",Splitter.SplitTextByPositions({0, 2}, false),{"Month", "Day"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each #date([Year],[Month],[Day])),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date"})
    in
        #"Removed Other Columns"
    Last edited by Comfy; Oct 12th, 2016 at 09:49 AM.

  3. #3
    Board Regular
    Join Date
    Mar 2016
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    This is what I have so far

    let
    Source = Folder.Files("desktop"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Combined Binaries" = Binary.Combine(#"Removed Other Columns"[Content]),
    #"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",",Encoding=1252]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", type number}, {"Column15", Int64.Type}, {"Column16", type number}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", type text}, {"Column21", type number}, {"Column22", type number}, {"Column23", type number}, {"Column24", type number}, {"Column25", type number}, {"Column26", type number}, {"Column27", type number}, {"Column28", type number}, {"Column29", type number}, {"Column30", type number}, {"Column31", type number}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Column4", Text.Trim}, {"Column5", Text.Trim}, {"Column6", Text.Trim}, {"Column7", Text.Trim}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "True Address", each [Column4]&" "&[Column5]&" "&[Column6]&" "&[Column7]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Column2"},Sheet1,{"LGE Tag"},"NewColumn"),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"MSD Locations"}, {"NewColumn.MSD Locations"}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Expanded NewColumn", {{"Column8", type text}}, "en-US"),"Column8",Splitter.SplitTextByPositions({0, 4}, false),{"Column8.1", "Column8.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column8.1", Int64.Type}, {"Column8.2", Int64.Type}}),
    #"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Column8.2", type text}}, "en-US"),"Column8.2",Splitter.SplitTextByPositions({0, 2}, false),{"Column8.2.1", "Column8.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Column8.2.1", Int64.Type}, {"Column8.2.2", Int64.Type}})
    in
    #"Changed Type2"

  4. #4
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    Have you looked at my example?

    Once you have split the date column you should add a custom column to reconstruct it.

  5. #5
    Board Regular
    Join Date
    Mar 2016
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    I am adding the custom column but it forces me to put a formula in right then. I tried pasting this portion of your code in for the formula but its not working


    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each #date([Year],[Month],[Day])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date"})
    in

  6. #6
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    ofc it won't work.

    Your data doesn't have the same column names that my example did.

    Code:
    let
    Source = Folder.Files("desktop"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Combined Binaries" = Binary.Combine(#"Removed Other Columns"[Content]),
    #"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",",Encoding=1252]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", type number}, {"Column15", Int64.Type}, {"Column16", type number}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", type text}, {"Column21", type number}, {"Column22", type number}, {"Column23", type number}, {"Column24", type number}, {"Column25", type number}, {"Column26", type number}, {"Column27", type number}, {"Column28", type number}, {"Column29", type number}, {"Column30", type number}, {"Column31", type number}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Column4", Text.Trim}, {"Column5", Text.Trim}, {"Column6", Text.Trim}, {"Column7", Text.Trim}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "True Address", each [Column4]&" "&[Column5]&" "&[Column6]&" "&[Column7]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Column2"},Sheet1,{"LGE Tag"},"NewColumn"),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"MSD Locations"}, {"NewColumn.MSD Locations"}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Expanded NewColumn", {{"Column8", type text}}, "en-US"),"Column8",Splitter.SplitTextByPositions({0, 4}, false),{"Year", "Other"}), //Assumed that date is YYYYMMDD
    #"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Position", {{"Other", type text}}, "en-US"),"Other",Splitter.SplitTextByPositions({0, 2}, false),{"Month", "Day"}), //Assumed that date is YYYYMMDD
    #"Added Date Column" = Table.AddColumn(#"Split Column by Position1", "Date", each #date([Year],[Month],[Day])),
    in
    #"Added Date Column"
    


    Try this.

  7. #7
    Board Regular
    Join Date
    Mar 2016
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    I appreciate all of your help, and I apologize for my ineptness but I am lost as to what to do from here. I have literally 0 experience with Power Query so I assure you I am trying my best. My code has changed quite a bit since my last reply as I slowly learn more. Below is what I have now, you can see that my two date fields have both been separated by yyyy,mm,dd. I don't know how to add your portion to my code. Do I just click add custom column and paste your code there? Or do I need to go into advance editor and paste the code there?

    Again, I apologize for all of the trouble. I understand if you want to give up.




    let
    Source = Folder.Files("C:\Users\calvertc\Desktop\LGE"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Combined Binaries" = Binary.Combine(#"Removed Other Columns"[Content]),
    #"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",",Encoding=1252]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", type number}, {"Column15", Int64.Type}, {"Column16", type number}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", type text}, {"Column21", type number}, {"Column22", type number}, {"Column23", type number}, {"Column24", type number}, {"Column25", type number}, {"Column26", type number}, {"Column27", type number}, {"Column28", type number}, {"Column29", type number}, {"Column30", type number}, {"Column31", type number}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Column4", Text.Trim}, {"Column5", Text.Trim}, {"Column6", Text.Trim}, {"Column7", Text.Trim}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "True Address", each [Column4]&" "&[Column5]&" "&[Column6]&" "&[Column7]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Column2"},Sheet1,{"LGE Tag"},"NewColumn"),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"MSD Locations"}, {"NewColumn.MSD Locations"}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Expanded NewColumn", {{"Column8", type text}}, "en-US"),"Column8",Splitter.SplitTextByPositions({0, 4}, false),{"Column8.1", "Column8.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column8.1", Int64.Type}, {"Column8.2", Int64.Type}}),
    #"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Column8.2", type text}}, "en-US"),"Column8.2",Splitter.SplitTextByPositions({0, 2}, true),{"Column8.2.1", "Column8.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Column8.2.1", Int64.Type}, {"Column8.2.2", Int64.Type}}),
    #"Split Column by Position2" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type2", {{"Column9", type text}}, "en-US"),"Column9",Splitter.SplitTextByPositions({0, 4}, false),{"Column9.1", "Column9.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position2",{{"Column9.1", Int64.Type}, {"Column9.2", Int64.Type}}),
    #"Split Column by Position3" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type3", {{"Column9.2", type text}}, "en-US"),"Column9.2",Splitter.SplitTextByPositions({0, 2}, true),{"Column9.2.1", "Column9.2.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position3",{{"Column9.2.1", Int64.Type}, {"Column9.2.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type4",{{"Column1", "Group Code"}, {"Column2", "Coll Master Account #"}, {"Column3", "Contract Account"}, {"Column4", "Add #"}, {"Column5", "Street"}, {"Column6", "City"}, {"Column7", "State"}, {"Column8.1", "Begin Year"}, {"Column8.2.1", "Begin Month"}, {"Column8.2.2", "Begin Day"}, {"Column9.1", "End Year"}, {"Column9.2.1", "End Month"}, {"Column9.2.2", "End Day"}, {"Column10", "Service Type (E_G_U)"}, {"Column11", "Installation Number"}, {"Column12", "Rate Category Code"}, {"Column13", "Rate Category Description"}, {"Column14", "Metered Demand kW"}, {"Column15", "Consumption kWh"}, {"Column16", "Total $"}, {"Column17", "Previous Read Date"}, {"Column18", "Current Read Date"}, {"Column19", "Read Code"}, {"Column20", "Read Code Description"}, {"Column21", "Billed Demand kW"}, {"Column22", "PF"}, {"Column23", "Base Metered Demand TOD"}, {"Column24", "Base PF TOD"}, {"Column25", "Base Billed Demand TOD"}, {"Column26", "Intermediate Metered Demand TOD"}, {"Column27", "Intermediate PF TOD"}, {"Column28", "Intermediate Billed Demand TOD"}, {"Column29", "Peak Metered Demand TOD"}, {"Column30", "Peak PF TOD"}, {"Column31", "Peak Billed Demand TOD"}})
    in
    #"Renamed Columns"

  8. #8
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    Quote Originally Posted by Craigc3814 View Post
    I appreciate all of your help, and I apologize for my ineptness but I am lost as to what to do from here. I have literally 0 experience with Power Query so I assure you I am trying my best. My code has changed quite a bit since my last reply as I slowly learn more. Below is what I have now, you can see that my two date fields have both been separated by yyyy,mm,dd. I don't know how to add your portion to my code. Do I just click add custom column and paste your code there? Or do I need to go into advance editor and paste the code there?

    Again, I apologize for all of the trouble. I understand if you want to give up.
    I'm learning too so don't worry about that

    When posting on forums though it's really helpful if the examples provided don't keep changing.


    Delete:
    Code:
    in
    #"Renamed Columns"
    
    Add a comma to the remaining last line.
    Then Add the following to the bottom.

    Code:
    AddStartDate = Table.AddColumn(#"Renamed Columns", "Start Date", each #date([Begin Year],[Begin Month],[Begin Day])),
    AddEndDate = Table.AddColumn(AddStartDate, "End Date", each #date([End Year],[End Month],[End Day]))
    in
    AddEndDate
    And if I may a small tip?

    PQ sometimes adds steps automatically (type detection) you can remove that step and the when all your transformations have taken place you can detect the type of all columns. This will reduce the number of lines in your M.

    So #"Changed Type", #"Changed Type1", #"Changed Type2", #"Changed Type3", #"Changed Type4" would just be one line instead of 5.

    /Comfy
    Last edited by Comfy; Oct 14th, 2016 at 04:22 AM.

  9. #9
    Board Regular
    Join Date
    Mar 2016
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    Quote Originally Posted by Comfy View Post
    I'm learning too so don't worry about that

    When posting on forums though it's really helpful if the examples provided don't keep changing.


    Delete:
    Code:
    in
    #"Renamed Columns"
    
    Add a comma to the remaining last line.
    Then Add the following to the bottom.

    Code:
    AddStartDate = Table.AddColumn(#"Renamed Columns", "Start Date", each #date([Begin Year],[Begin Month],[Begin Day])),
    AddEndDate = Table.AddColumn(AddStartDate, "End Date", each #date([End Year],[End Month],[End Day]))
    in
    AddEndDate
    And if I may a small tip?

    PQ sometimes adds steps automatically (type detection) you can remove that step and the when all your transformations have taken place you can detect the type of all columns. This will reduce the number of lines in your M.

    So #"Changed Type", #"Changed Type1", #"Changed Type2", #"Changed Type3", #"Changed Type4" would just be one line instead of 5.

    /Comfy
    Sorry if this comment posts twice, internet is acting up

    First: sorry for the changes at the last minute I am presenting this query later this afternoon in hopes of convincing the company that power query is way better than what's being used for this process now (access)

    Second:. HOLY CRAP!!!! IT WORKED!!!!! I cannot thank you enough. I am loving power query so far and that was going to ruin a lot if I couldn't get that sorted out. I had a crappy work around fix where I added columns to the worksheet and put the date formula in but the formula wouldn't copy down as new data was entered.

    You're the man!!!!!

  10. #10
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date values stored as text to dates

    You're Welcome! Happy to help.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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