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.
I have a column with a text string as a value. At the end of the text string there is a date (MM/DD/YYYY)....sometimes. Each cell in the column is formatted like:
"String of text DD/MM/YYYY" or just simply "String of text"
End goal: Use a "New Measure" to create a "Calculated Column" that will return the date string if there is a date and nothing if there is no date because I need to format the column as a date to get my visuals and slicers to render properly.
Complications
- I need to use a measure because it is a direct query data type
- Not every string in the original column has a date string at the end
What I've tried:
Using the RIGHT function to grab the last 10 characters in the string. While this will get the right information, I can't format the column as a date or everything breaks (Error code says "Conversion failed when converting date and/or time from character string).
**I know this issue is because some of the fields don't have the date string at the end so Power BI won't convert the values to date if there are text characters. If there is a way to tell it to nullify any non-date values, this would potentially work?
I also tried to use an IF statement but I can't figure out what I would use to make it work.
Thank you for any guidance
Solved! Go to Solution.
In case anyone stumbles on this in the future -
I ended up having to edit the query as it pulled the data in to remove any entires in the column that did not have "/" - then I was able to grab the right digits and convert the column to date.
You can use IsError function:
myDate = if(IsError(myTable[Text]), BLANK(), DATEVALUE(myTable[Text]))
myTable[Text] in this case will right function to grab 10 characters.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I think this would work but my feed is a DirectQuery which doesn't permit ISERROR functions.
In case anyone stumbles on this in the future -
I ended up having to edit the query as it pulled the data in to remove any entires in the column that did not have "/" - then I was able to grab the right digits and convert the column to date.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |