Condition If Date column is empty against Id

[Junaid11]

Hello,

I want to create a column in which I want if Date is there against ID then it should show Ready otherwise not ready. If ID is same and one cell has date and other opposite cell does not have the date then still it should show not ready. Below is the data:

ID	Date
A1	23/02/2021
A1	18/01/2020
A2
A2	01/09/2020

The outcome is below that I am looking for if ID of both rows are same and one row has date and other does not have it then it should shownot ready as below:

ID	Date	Outcome
A1	23/02/2021	Ready
A1	18/01/2020	Ready
A2		Not Ready
A2	01/09/2020	Not Ready

It would be quite useful if you could help.

Thank you

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(CALCULATE(COUNTBLANK(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])))=1,"Not Ready","Ready")

Hope this helps.

[danextian · Accepted solution]

Hi @Junaid11 ,

Aside from merging queries like @jsaunders_zero9  has suggested is using the Table.Group function in Power Query.

• First a custom column is created that will return 1 if [ID]  is null.
• Second, the whole table is grouped by [ID] so that transforms the current table into two columns: [ID] and another column with tables that contains all the records prior to grouping.
  
• Third, create a custom column that will sum column created in the first step per row ID.
• Last, expand the column of tables created in the second step to show just the date.
  

Here 's a sample M Script to be pasted on a blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyMtY3MNI3MjAyVIrVgYoZWugbGILEDCBiRkAxBQQTKGdgCZWOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "BlankDate", each if [Date] = null then 1 else null, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ID"}, {{"Count", each _, type table [ID=nullable text, Date=nullable date, BlankDate=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Ready/Not Ready", each if List.Sum([Count][BlankDate]) <> null then "Not Ready" else "Ready", type text),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom1", "Count", {"Date"}, {"Date"})
in
    #"Expanded Count"

Alternatively, you can create a calculated column using DAX that will scan the table and return whether there is a blank date per row id.

Ready/Not Ready = 
VAR CountOfBlank =
    CALCULATE (
        COUNTBLANK ( 'Table (3)'[Date] ),
        ALLEXCEPT ( 'Table (3)', 'Table (3)'[ID] )
    )
RETURN
   IF ( CountOfBlank > 0, "Not Ready", "Ready" )
   

[jsaunders_zero9]

Hi @Junaid11 

Here is a quick and easy way to acheive in PQ.

Junaid11_sample.pbix

[Junaid11]

Hello @jsaunders_zero9 ,

I did not mean that I have two tables. I have first table and secondl table is about expected outcome. I have millions of IDs and I cannot put each of them there one by one like you did.

Thank you

[jsaunders_zero9]

I understand what you meant, I also only have 1 table of data in the sample. The second table is derived from the first and filtered to show only ID's where there is no date and then merged back on the first.

Thank you