Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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 can you not do this transformation on the server side using native server language, for example SQL?
@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
@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
@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
how would i check this?
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
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 🙂
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 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
@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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |