Forum Discussion
Help needed for a date format conversion
Use the "with Locale" setting. Click on the ABC data type in the upper left of the column, then select "using locale" at the very bottom of the list, then set it like below:
I just use Bahamas because I know it uses DD/MM/YY as the format and will work. Use whatever locale your data is coming from. It returns this:
If you need further help, please post data we can use in a table format per links below.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
Hi maclura,
alternatively, you can use Table.TransformColumns instead of Table.TransformColumnTypes. This is particularly helpful when dealing with multiple date formats in a single dataset:
Table.TransformColumns(Source,{{"Column1", (x) => Date.ToText(Date.From(x, "en-GB"), "yyyy-MM-dd")}})Replace the formula in your type transforming step in the Advanced editor to the above and replace Source in the formula to the name of the preceding step in your query.
Kind regards,
JB
Hi maclura,
alternatively, you can use Table.TransformColumns instead of Table.TransformColumnTypes. This is particularly helpful when dealing with multiple date formats in a single dataset:
Table.TransformColumns(Source,{{"Column1", (x) => Date.ToText(Date.From(x, "en-GB"), "yyyy-MM-dd")}})
Replace the formula in your type transforming step in the Advanced editor to the above and replace Source in the formula to the name of the preceding step in your query.
Kind regards,
JB
Anonymous you still have to convert the data type though, correct? The ABC/123 data type (any) will not be usable as a date in PowerBI. Can you do that with the TransformColumns step by adding the data type as the last parameter?
Thanks, Ed.
Precisely, but it is not clear will this be a date or text format from the tech description. Conversion is a reasonably simple step. My impression that it should be text. Technically, converting the output from the formula in my previous post to Date will most likely return an error as the yyyy-MM-dd format may not be suitable/clear for PBI.
Kind regards,
JB