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'm currently trying to extract specific values from a column into a new column in power query. The column consists of differing values (dates, numbers, words) and I would only like the new column to contain the dates from this existing column. I've tried using extract, lookupvalue, and creating a column from example but have been unable to get solely the dates in the new column. Any help is appreciated, thank you
Solved! Go to Solution.
Did you ensure you were replacing the other step, and not creating a new one?
Otherwise, you could try adding a Custom Column in the add column tab, and use the code:
if
Text.Contains([Column1] ,"-") or Text.Contains([Column1] ,"/") or Text.Contains([Column1] ,"\")
then
try
Date.FromText([Column1])
otherwise
null
else
null
Change the red to your column.
It first looks for any of - / \ and if it finds them, tries to transform it to a date
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
Duplaicate the column by Right click > duplicate column.
Click on the new column, and change the data type of your new column to Date in Transform > Data Type:
Right click on the nbew column and Replace Errors. Type in the word null.
Now your new column will only contain the dates !
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
Hello, thank you for the reply! This seems to work for the values that are words (such as "Units" in the screenshot of the column), but for values that are numbers (such as "5800" in the screenshot) they are turned into date values rather than errors. As a result, they do not get changed to null when errors are replaced. Could I be missing something here?
Dates are stored as integers, so that would make sense - Try to change to "Date/Time/Timezone" instead of just date
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
Unfortunately this resulted in the same output, where the numbers changed to date/time/timezone rather than error.
Did you ensure you were replacing the other step, and not creating a new one?
Otherwise, you could try adding a Custom Column in the add column tab, and use the code:
if
Text.Contains([Column1] ,"-") or Text.Contains([Column1] ,"/") or Text.Contains([Column1] ,"\")
then
try
Date.FromText([Column1])
otherwise
null
else
null
Change the red to your column.
It first looks for any of - / \ and if it finds them, tries to transform it to a date
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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.