Forum Discussion
Data Type conversion problem for DATE in Power Query on au.finance.yahoo.com
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
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.
6 Replies
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.Thanks moncx, the step of replace value for Sept with Sep.. it works.
schch
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)
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 worksHi 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
