Converting date values stored as text to dates

[Craigc3814]

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?

 

[Comfy]

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:

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"

 

[Craigc3814]

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"

 

[Comfy]

Have you looked at my example?

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

 

[Craigc3814]

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

 

[Comfy]

ofc it won't work.

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

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.

 

[Craigc3814]

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"

 

[Comfy]

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:

in
#"Renamed Columns"

Add a comma to the remaining last line.
Then Add the following to the bottom.

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

 

[Craigc3814]

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:

in
#"Renamed Columns"

Add a comma to the remaining last line.
Then Add the following to the bottom.

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!!!!!

 

[Comfy]

You're Welcome! Happy to help.