Convert Date into Proper date

[sadath]

Hi

i have table from web source
Source = Web.Page(Web.Contents("http://www.accuweather.com/en/sa/dammam/297095/january-weather/297095?monyr=1/1/2016&view=table")),

Date column showing Fri 1/1 , Sat 1/2
how can i convert into proper date (dd/mm/2016) eg. 1/1/2016, 2/2/2016

 

[Matt Allington]

Chop off the day names from the front, concatenate /2016 to the back, then convert to date format

 

[citizenbh]

As recommended by Matt try this:

let
    Source = Web.Page(Web.Contents("http://www.accuweather.com/en/sa/dammam/297095/january-weather/297095?monyr=1/1/2016&view=table")),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    Data = #"Removed Other Columns"{0}[Data],
    #"Split Column by Delimiter" = Table.SplitColumn(Data,"Date",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Date.1", "Date.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type text}, {"Date.2", type text}, {"Hi/Lo", type text}, {"Precip", type text}, {"Snow", type text}, {"Forecast", type text}, {"Avg. HI / LO", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year", each 2016),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Year", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each [Date.2]&"/"&[Year]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom",type date}},"en-US")
in
    #"Changed Type2"

Pay attention to the "en_US" in order to obtain the required format "DD / MM / YYYY"

 

[Craigc3814]

Reading through the forum just trying to gain knowledge, is this line (#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom",type date}},"en-US") the equivalent of changing the date using locale?

 

[MarcelBeug]

Almost: it is the equivalent of changing the data type for column "Custom" to date, using locale.
Probably this is what you intended to ask?

 

[Craigc3814]

Correct, I have been reading/working through the book M is for data Monkey that you suggested to me. Slowly picking things up.