Data Type conversion problem for DATE in Power Query on au.finance.yahoo.com

[Schch]

Hi,

I found that the Data type conversion for Date in Power Query did not work from Sept for the web source :

 https://au.finance.yahoo.com/quote/%5EHSI/history/.

Any clue to this

[moncx · Accepted solution]

Hey @Schch ,

as @Alex87 said promote first row to headers, you can find how to do that here: Promote or demote rows and column headers (Power Query) - Microsoft Support

And then you get error because 'Sept' is not official shortened version of september - it should be 'Sep'. So before you change column type to date add a step and replace 'Sept' to 'Sep' (instructions: Replace values (Power Query) - Microsoft Support) or just use this M code:

= Table.ReplaceValue(#"Changed Type","Sept","Sep",Replacer.ReplaceText,{"Date"})
After that just check if other months use correct short version of the month and if not change it in the same manner before converting to date type.

Hope it helps!

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[BobWong1]

Hello,

May I know why this function;

= Table.AddColumn(#"Added Custom4", "Month Name", each Date.ToText([DateKey],"MMM"))

returns Sept i.s.o. Sep?

Other short month name works perfectly (ie, Jan, Feb, Mar, Apr, May, Jun, July, Aug, Oct, Nov, & Dec)

[Schch]

Thanks moncx, the step of replace value for Sept with Sep..  it works.

schch

[Ritaf1983]

Hi @Schch 

I wasn't able to connect to the site, so for demonstration purposes, I downloaded your table as a CSV. From there, the steps are: 1. Promote the headers to the top row, and 2. Convert the date field to a local date.

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[PijushRoy]

Hi @Schch 

1 Please delete the change type step
2. Use the first row as a header option to select Date, Open, High etc as a column header

3. Set a locale or region for data (Power Query)
Step by step information - https://www.myonlinetraininghub.com/change-type-using-locale-with-power-query

Please let me know if above steps not works

[Alex87]

Hello @Sch ,

From what I see your first row needs to be promoted as headers (eg column 1 should be called Date)

In which concerns the date type, try using locale date (right click on column title, change type, using locale, date & locale)