Error in custom column in query editor

[ny326n]

Hello everyone!

I have a table with last active dates: 

User name , Last active date

What I want to do is group customers according to last active date; however, I have not found a way to do this except in the query editor which I'm not very familiar with. 

What I have done so far is added a custom column in query editor: DateTime.LocalNow()-[Last Active Date]

Then I've inserted a round up column

Then I've created a conditional column that sorts the customers into groupings 1-2 months, 3-6 months, etc. 

The problem though is that the Last active date column does not have a value on all rows which creates an error on those empty rows. I need to have some kind of IFERROR function here. How do I solve this?

Thank you very much in advance!

[BalaVenuGopal]

HI @ny326n,

step 1 : Go to data model (Edit queries) 

step 2 : Select the date column then go to properties and remove empty.

Hope this will resolve your issue please make sure to like this answer if it works for you heppy to help !!!!

[prateekraina]

Hi @ny326n,

In order to remove errors, you can click on your column and then click on "Remove Rows" > "Remove Errors"

Hope this helps,

Also, if you do not want to achive this in Query Editor, there is a  very simple way of doing this by creating a measure.

GroupBYDate = SUMX(SUMMARIZE(Table1,Table1[Date],"CountOfUsers",COUNT(Table1[User])),[CountOfUsers])

Replace column and table names as per your dataset and it should work. Here are the screenshots:

@prateekraina

[ny326n]

Thank you for you reply.

However, I do not want to remove the rows that contains an error, because I still like to keep the client name in the report although it does not have a date. I only want to change the error to for exampe "No date" so I don't have to see the error each time I refresh the data.

[prateekraina · Accepted solution]

@ny326n,

What about the measure option then? That can be done i guess.
Also, you can replace errors with "No Data" as you mentioned. Go to "Transform" tab and click on "Replace Values" > "Replace Errors"



Please read my previous reply till end.

[ny326n]

Thanks a lot! This works. I will also try the measure option, it might be better.

[prateekraina]

Yes, do try that. It will make your report less complex and fast.

Also, kindly give KUDOS to my answer if you think i have helped you.