Change column type to Date in Power Bi

1
votes

I had added new table to my Power Bi file that contains a column with date with the following format: dd/MM/yyyy for example: 17/08/2017.

In power Bi this column is Text type, and when I try to convert to date it came with the following error: 

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
    17/08/2017

how can I change its type or format this text on new column and change the type if possible?

1
powerbidax
Can you give us a copy of that column? Maybe have a extra character or something that are not allowing the conversion... – ptfaferreira

1 Answers

2
votes

First try altering the Regional Settings for the Current File to a country where dd/MM/yyyy is the standard date format. Like the Netherlands (or maybe your own country).
Choose [Options and settings > Options] on the File-tab. When you're done, refresh.

enter image description here

If changing the settings doesn't work for you, you can use the Power Query Editor to split the date column and then merge in the correct order. Perform the following steps:

  1. Select the date column and use Split Column (Transform tab). Split by delimiter [/] and select [Each occurance of the delimiter]
  2. Merge the three columns again by using Merge Columns. The columns will merge in the order that you select them. For the separator choose custom and type /
  3. Change the datatype to Date

This part of the script should look somthing like this:

#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Previous step", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
#"Merged Columns2" = Table.CombineColumns(#"Split Column by Delimiter",{"Date.2", "Date.1", "Date.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
#"Changed Type5" = Table.TransformColumnTypes(#"Merged Columns2",{{"Merged", type date}})