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

Replacing a value in direct query mode

Hi, is it possible to add a column and add dax that would include something like.. if text.contains([date], "1800" then "" else ([date])? The problem is there is a default date in the view I'm using and we are unable to update the view or build a new one at this time. The default date replaces anything that has a null value.  I am trying to make the default date show as null, but I'm not sure how to do that in direct query mode. Is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Replacing a value in direct query mode

@KWelsh8144 ,

Please perform the steps below.

1. Make sure you have changed the type of DueDate field to Text in Power BI Query Editor.
1.png

2. Load data to Data view of Power BI Desktop, then create the following column.

Updated Due Date = if(find("1899",vProjects[DueDate],1,0)>0, BLANK(),vProjects[DueDate])


3. Change the data type of the new column  to Date.
2.png

Regards,
Lyida

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Highlighted
Microsoft
Microsoft

Re: Replacing a value in direct query mode

@KWelsh8144,

Please use the DAX as below.

Column = IF(FIND("1800",'Table'[Date],1,0)>0,"",'Table'[Date])



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft
Microsoft

Re: Replacing a value in direct query mode - Status changed to: Delivered

 
Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Replacing a value in direct query mode

@v-yuezhe-msft Thanks - however that turn is returning an error that stats "Expressions that yield variant data-type cannot be used to define calculated columns." How can I fix that? The date that I'm trying to get rid of is actually 12/30/1899. So I tried with "1899" and also "12/30/1899" but neither are working. I'm not sure why it's throwing the error.

Highlighted
Microsoft
Microsoft

Re: Replacing a value in direct query mode

@KWelsh8144,

Make sure the data type of your field is text rather than date.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Replacing a value in direct query mode

@v-yuezhe-msft So I changed the data type to text, but then it gives me a long date with time. For example, it now shows as: May 16 2018 12:00AM. I also use the dates in the filters, so its appear as a drop down list instead of a 'slider' style now that i've changed it to text. Is there anyway to 1) make the date only show as 05/16/2018  and 2) so that the dates can be used in a filter the way the other dates are with a date range/slider instead of a dropdown

Highlighted
Microsoft
Microsoft

Re: Replacing a value in direct query mode

@KWelsh8144 ,

Click on your date field, then change data type and format under modeling tab.
Capture.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Replacing a value in direct query mode

@v-yuezhe-msft If I change the new column you indicated I needed, to show the invalid dates as null like mentioned above, to a date, it shows the invalid date as 1/1/1900 now.

 

This was the new column's dax i used:

**Updated Due Date = if(find("1899",vProjects[DueDate],1,0)>0, "",vProjects[DueDate])
 
If i change that column to date, the dates with "1899" show as 01/01/1900. What can I do?
Highlighted
Microsoft
Microsoft

Re: Replacing a value in direct query mode

@KWelsh8144 ,

Please perform the steps below.

1. Make sure you have changed the type of DueDate field to Text in Power BI Query Editor.
1.png

2. Load data to Data view of Power BI Desktop, then create the following column.

Updated Due Date = if(find("1899",vProjects[DueDate],1,0)>0, BLANK(),vProjects[DueDate])


3. Change the data type of the new column  to Date.
2.png

Regards,
Lyida

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Replacing a value in direct query mode

@v-yuezhe-msft  Thanks! This worked!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors