cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rwcampbell
Helper I
Helper I

Direct Query Text String to Date Help

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!

1 ACCEPTED SOLUTION
TheoC
Memorable Member
Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

13 REPLIES 13
smpa01
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


TheoC
Memorable Member
Memorable Member

@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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

@rwcampbell did you try this approach? 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

amitchandak
Super User
Super User

@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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

how would i check this?

TheoC
Memorable Member
Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Thanks @TheoC  how do i check the below 

 

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

TheoC
Memorable Member
Memorable Member

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:

TheoC_1-1634868421708.png

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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

rwcampbell_0-1634900962879.png

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

TheoC
Memorable Member
Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

TheoC
Memorable Member
Memorable Member

@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.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

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
Memorable Member
Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors