Get 1 Date per code in table

[PBI_Monkey]

Hi all,

I have a table with the following columns:

Code Date        Time         code isdefault
A 28/03/2023 0700-2100 N FALSE
A 28/03/2023 0700-2100 T TRUE
A 29/03/2023 0700-2100 N FALSE
A 29/03/2023 0700-2100 T TRUE
A 30/03/2023 0700-2100 N FALSE
A 30/03/2023 0700-2100 T TRUE
A 31/03/2023 0700-2100 N FALSE
A 31/03/2023 0700-2100 T TRUE
A 1/04/2023 0700-2100 T TRUE
A 2/04/2023 0700-2100 T TRUE
A 3/04/2023 0700-2100 T TRUE
A 4/04/2023 0700-2100 T TRUE
A 5/04/2023 0700-2100 T TRUE

You can see that some dates are the same with a different value for code (N/T) and different value for isdefault (True/False)

I'd like to show the table like this, but struggling to create a measure/column to create this:

Code Date Time code         isdefault
A 28/03/2023 0700-2100 N FALSE
A 29/03/2023 0700-2100 N FALSE
A 30/03/2023 0700-2100 N FALSE
A 31/03/2023 0700-2100 N FALSE
A 1/04/2023 0700-2100 T TRUE
A 2/04/2023 0700-2100 T TRUE
A 3/04/2023 0700-2100 T TRUE
A 4/04/2023 0700-2100 T TRUE
A 5/04/2023 0700-2100 T TRUE

The resulting table should always show the row when 'isdefault' = FALSE & 2 rows with same date

or if is there is only 1 unique date per row and isdefault = TRUE

Any ideas would be great 🙂

Thank you

[Ahmedx]

watch my video
https://1drv.ms/v/s!AiUZ0Ws7G26RhmqC8FHuvTvRNzs7?e=4Pp6TN
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhmkCXBXRqesgOkU5?e=LGOEaB

= Table.Group(#"Changed Type", {"DateTime"}, {{"Count", 
each Table.FirstN(_,1)
}})

[PBI_Monkey]

Hi @Ahmedx ,

Thanks for the cool reply.

I tried your solution but it doesn't work. Seems to be excluding alot of data.

I basically have 2 subsets of data (2 queries which I append into 1) - See screenshot

The resulting table must have all data from Table 1 and not matching rows from the 2nd Table.

So from table 2 in this example, I want to show the rows from 1st April to 5th April.

Business   Date               Time           Code     isdefault
A                28/03/2023 0700-2100  N          FALSE
A                29/03/2023 0700-2100  N          FALSE
A                30/03/2023 0700-2100  N          FALSE
A                31/03/2023 0700-2100  N          FALSE
A                1/04/2023 0700-2100     T          TRUE
A                2/04/2023 0700-2100     T          TRUE
A                3/04/2023 0700-2100     T          TRUE
A                4/04/2023 0700-2100     T          TRUE
A                5/04/2023 0700-2100     T          TRUE

Thanks

[Ahmedx]

If I understand you correctly, then you first need to do
1) Right Anti Join (table1,table2)
2) combine what you got with 1 table
---------

I can't do this without a real example.
read this:
https://gorilla.bi/power-query/join-types/

[PBI_Monkey]

Hi @Ahmedx ,

I tried the anti join and then appended the result with Table 1.

I get strange results:

I've attached the .PBIX 

https://www.dropbox.com/s/pvqgf5jn616249i/WR.pbix?dl=0 

Thanks

[Ahmedx · Accepted solution]

Is this what you are looking for?
https://1drv.ms/u/s!AiUZ0Ws7G26Rhnn0tywq_Ay2nvOE?e=9zQPiw

[PBI_Monkey]

Thank you @Ahmedx. Seems I have to include 3 fields to join in the right-anti join.

Thanks, your solution worked.

[KeyurPatel14]

Hi @PBI_Monkey ,

can you please use Power Query and Remove duplicates from the Date Column?
Try this and let me know if you have any queries.

[PBI_Monkey]

Thanks for the reply @KeyurPatel14 

Removing duplicates won't work as there is a code which is different on the row with the same date