Group by If Blank, then blank else Max Date

[NBOnecall]

Hi,

I have a table that I am importing and would like to group it by its Internal ID. Each ID can have up to 4 Phase Out Dates and would like to display the Max Date, unless there is a blank. If there is a blank I would like the blank to stay. 

I don't know enough about Power BI to know if I can do this. Here is the group by screenshot choosing the Max date, but would want almost an if statement of sorts in there.

Thanks,

Noel

[Anonymous]

Hi

One trick I've used is:

1. replace the nulls/empty cells with extrodinarily high dates (12-Dec-4444) first,

2. then group by max as per normal,

3 and then undo step 1 by replacing that extrodinarily high date with null/empty again.

Its not foolproof for any dataset, but can be reasonably managed and only adds two extra steps to your Query Steps. 

Could even go further and have this 'highDate' assigned as a parameter, and have a separate query checkign for count of dates > this 'highDate' parameter to doubly make sure you're setting the ceiling high enough. 

🙂

[pbeye]

What do you get back if you proceed with what you showed you in the picture? 

[NBOnecall]

I get the max date, even if there is a null phase out date for that same internal ID.

For example Internal Id 10008 has a phase out day at certain locations of 8/05/2019, but also has blank ones. I would like to see the blank appear before a date, if there is a blank.

[pbeye]

The way I figured this out is a bit convoluted, but it worked for me. If you can load the table ungrouped to begin with then it should work for you as well. It might not work great if your dataset is very large. 

1. Create a copy of your data (duplicate it) so you have 2 sets.
2. Set 1 - filter down to NULL Dates. Group by your ID and take the max of your dates
3. Set 2 - filter down to populated dates. Group by your ID and take the max of your dates.
4. Merge as new on your ID column
5. Expand Set 2.
6. Add custom or conditional column: if Set 1 ID is null then Set 2 ID else Set 1 ID.
7. Using same formula, add another column but return date instead of id.
8. Remove all other columns except for your two new columns you created. 

If there's any easier way to do it I'd love to know how!

[NBOnecall]

Can you do these steps in a query?

Add custom or conditional column: if Set 1 ID is null then Set 2 ID else Set 1 ID.
Using same formula, add another column but return date instead of id.
Remove all other columns except for your two new columns you created. 

I am trying to merge this final query to a direct query. As you can't create a calculated column on a direct query.

Thanks!

[pbeye]

Sorry, I'm not sure what you mean. All of the steps should be done in the query editor. 

[NBOnecall]

Ah, I see it now. I was thinking you did those last three steps as a calculated column in the table view and not the edit query section.

[dax]

Hi NBOnecall,

If possible, could you please inform me your requirement in details? Then I will help you more correctly. In addition, because you use Max value as group, so it will remove null value and only show max date in group.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.