Hi Macca,

You have two places where the dates are getting messed up. First when you paste the dates into Excel, Excel is converting them, then when you load the table into Power Query you need to move the original 'Changed Type' step that is applied automatically when you load your data, before applying the Changed Type with Locale.

You need to either use Get Data From Web to get the data from the web page direct to Power Query and then into the Excel Table, or when you paste the values from the web page, you should paste them to a CSV file and then get the CSV file with Power Query, this way the dates don't get messed up by Excel in the first instance of pasting.

Mynda

I stumbled upon a solution to solve this in excel and avoid using .csv files. I used a step which first converted the date column in to "text type" and then I replaced the 00:00:00 with "empty". After that the using Date Locale recognised and transform the dates correctly into the format I needed (from a text type column into a date type column). I have uploaded an updated excel file.

I'm not sure this has worked as you're expecting. I think the dates in Sheet1 that start with 12/ are December dates, but in the query in Sheet2 they are entered as 12th of February through November. 

There appears to be some discrepancy here between what you are seeing and what I am seeing. In my workbook they are shown as dates Nov-Dec. I attached a screenshot of sheet 2 of the query also showing the date format. I have been using this query for about 2 weeks now and have not detected any errors.

Nevertheless your comment indicates to me the the solution is not robust and may not work in every occasion for everyone. Caution needs to be taken.

No screenshot attached. What is your locale date format, dd/mm/yyyy or mm/dd/yyyy?

Mynda 

Screenshots attached now.

Locale date format is dd/mm/yyyy

Hi Craig,

I think I must have had your original workbook open and not noticed that the revised workbook had 'a' appended to the end of the workbook name, as I can see the dates correctly in Book2a.xlsx now!

That said, it still more efficient to get the data direct from the website with Power Query than copying and pasting and then having to fix the format with multiple steps. 

Mynda

I agree it would be better and more efficient to get the data from the website with Power Query but it does not work for me. I don't think they are html tables but javascript or something.

Here are example of 2 links of data I need.

https://www.barchart.com/stocks/quotes/$MAHX/price-history/historical

https://www.barchart.com/stocks/quotes/$MALX/price-history/historical

Would appreciate if you could give it a try. Maybe it works for you.

Ah, yes, that's a JavaScript table.

If you have a Microsoft 365 version of Excel you can use the new STOCKHISTORY function to get that data.

Mynda

Hi maccra,

That website provides an API so you may be able to use that to get the data you need. 

https://www.barchart.com/ondemand/api

It says that they provide tools to access the API in languages like JavaScript, PHP or Python.  But as it's a REST API you can just issue HTTP requests (just like using a web browser) to access data.  For example this request gets historical data for IBM returned in JSON format

IBM History

Please read this post which describes how to access an API in Power Query. 

Getting started with API's in Power Query

From the IBM example it looks like they are using very simple authentication where the API key is included in the HTTP request (the web URL).  This is the same as the NASA example in my blog.

Regards

Phil