Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Junaid11 ,
Aside from merging queries like @jsaunders_zero9 has suggested is using the Table.Group function in Power Query.
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" )
Proud to be a Super User!
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.
Hi @Junaid11 ,
Aside from merging queries like @jsaunders_zero9 has suggested is using the Table.Group function in Power Query.
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" )
Proud to be a Super User!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |