Direct Query Text String to Date Help

[rwcampbell]

hello

i have data that is coming from our servers as a text string of yyyymmdd but i would like it to be dd mmmm yyyy but when i do it wont let me on the editor and throws an error of wanting it in local which is no use to me.

i would like it to run with the errors showing as 0 or blank but i have hit a brick wall in options.

All help will be great!

[smpa01]

@rwcampbell  can you not do this transformation on the server side using native server language, for example SQL?

[TheoC]

@rwcampbell if you have tried @amitchandak solution and it doesn't resolve (it likely will solve your problem though), you can use dax to format the output as per this post: https://community.powerbi.com/t5/Desktop/How-to-convert-text-20150714-to-date/m-p/90886

[TheoC]

@rwcampbell did you try this approach? 

[amitchandak]

@rwcampbell , check if that format is taken as date. Because then you choose a display format from the format in column tools

Also, check if format function is allowed in the direct query, that can help

[rwcampbell]

how would i check this?

[TheoC]

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-data-categorization

[rwcampbell]

Thanks @TheoC  how do i check the below 

Also, check if format function is allowed in the direct query, that can help

[TheoC]

Hi @rwcampbell 

You can check this by clicking on the the respective field in the Fields pane, then going up to the ribbon and clicking "Column Tools".  Under that tab, you will find the Data Type and Format for the specific field.  Refer below:

Basically, if Data Type is Text and Format is Text, then you can create a Calculated Column using the following:

Col_Date = DATE ( LEFT ( [ColumnName] , 4 ) , MID ( [ColumnName] , 5 , 2 ) , RIGHT ( [ColumnName] , 2 ) )

 Alternatively, if your Data Type is "Date", then all you need to do is click on the "Format" drop down, and select the Date Format that you want.

Hope this helps 🙂

[rwcampbell]

@TheoC this is what i get when i created the collumn 

[TheoC]

@rwcampbell I will look into this error but I am wondering if it is a permissions issue. I'll get back to you shortly!

[TheoC]

@rwcampbell https://rohitvangala.wordpress.com/2017/02/02/error-the-exception-was-raised-by-idatareader-interfac...

please refer to this link to better understand the error.

[rwcampbell]

Hi @TheoC it looks like there is 0s on the collum coming from the spreadsheet - how can i fix this without turning the report into import mode only?

[TheoC · Accepted solution]

Hi @rwcampbell, you should be able to fix this by adjusting the Calculated Column to the following:

Col_Date = IF ('TableName'[ColumnName] = 0 , BLANK () , DATE ( LEFT ( [ColumnName] , 4 ) , MID ( [ColumnName], 5 , 2 ) , RIGHT ( [ColumnName] , 2 ) ) )

Basically you want to wrap the Calculated Column in a formula that says if it is 0 then return nothing, otherwise convert YYYYMMDD to the date in the requested date format.

Let me know how it goes.

Theo