Forum Discussion

PBI_Monkey's avatar
PBI_Monkey
Helper I
3 years ago
Solved

Get 1 Date per code in table

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

Need Help

8 Replies

  • KeyurPatel14's avatar
    KeyurPatel14
    Responsive Resident
    3 years ago

    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's avatar
      PBI_Monkey
      Helper I
      3 years ago

      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

    • PBI_Monkey's avatar
      PBI_Monkey
      Helper I
      3 years ago

      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