cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ny326n Frequent Visitor
Frequent Visitor

Error in custom column in query editor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
prateekraina New Contributor
New Contributor

Re: Error in custom column in query editor

@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"

Solution1.PNG

Please read my previous reply till end.

6 REPLIES 6
prateekraina New Contributor
New Contributor

Re: Error in custom column in query editor

Hi @ny326n,

 

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

Hope this helps,

 

 

Solution1.PNG

 

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:

 

Solution1.PNGSolution2.PNG

 

@prateekraina

ny326n Frequent Visitor
Frequent Visitor

Re: Error in custom column in query editor

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.

 

 

Re: Error in custom column in query editor

HI @ny326n,

 

step 1 : Go to data model (Edit queries) 

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

Remove Empty.pnggroup by.pngFinal Output.png

 

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

prateekraina New Contributor
New Contributor

Re: Error in custom column in query editor

@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"

Solution1.PNG

Please read my previous reply till end.

ny326n Frequent Visitor
Frequent Visitor

Re: Error in custom column in query editor

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

prateekraina New Contributor
New Contributor

Re: Error in custom column in query editor

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 376 members 3,433 guests
Please welcome our newest community members: