Returning a "Yes/No" To a Cell Based On One Of The Last 4 Days Values

[Ramh84]

Dears~,

I will appreciate your support on below complex problem. 

In one of my power BI file, I have a query that contains similar data to below table.

I am intersted to have a cutom column called "To Be Checked" that contains " Yes or No".

"Yes" should be returned to the cell of a given date whenever one of the criteria below is met:

      1- if the same day value of "#of Runs" column is greater than 0, or

      2- if, at least, one of the last 4 days values for the same "Unique_ID_2" column is greater than 0,

Otherwise, the cell should be filled with "No"

I hope that I have made it clear and simple 🙂

[dufoq3 · Accepted solution]

Hi, I'm not sure about 1st condition but I've implemented only 2nd with expected result:
If you want to add another condition - please explain more in details.

Create Blank Query in PQ and paste there this code if you want to see it step by step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck0sLokPdFTSUTIEYX1DfSMDIxOlWB2olG8QUNgAXSo8FSjl6QcUNkKX8ssvKsmID3bEpg1hGUTKCJuJhuhSSCZiyKGbaIzNRAwpDDca4zbRBLeJJnjcaILbRFPcJpricaMpbhPNcJtohsdEM9wmmuOOGXM8JppjMxGizQK3Gy3wmAiTiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unique_ID_2 = _t, #"# Of Runs" = _t, Date = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"# Of Runs", Int64.Type}, {"Date", type date}}, "en-US"),
    Ad_DateMinusFiveDays = Table.AddColumn(#"Changed Type", "Date +4 Days", each Date.AddDays([Date], 4), type date),
    #"Grouped Rows" = Table.Group(Ad_DateMinusFiveDays, {"Unique_ID_2"}, {{"All", each _, type table [Unique_ID_2=nullable text, #"# Of Runs"=nullable number, Date=nullable date, #"Date -5 Days"=date, Index=number]}, {"All2", each _, type table [Unique_ID_2=nullable text, #"# Of Runs"=nullable number, Date=nullable date, #"Date -5 Days"=date, Index=number]}}),
    #"Expanded All2" = Table.ExpandTableColumn(#"Grouped Rows", "All2", {"# Of Runs", "Date", "Index"}, {"# Of Runs", "Date", "Index"}),
    Ad_LastFiveDays = Table.AddColumn(#"Expanded All2", "Last Six Days", each Table.SelectRows([All], (r)=> [Date] >= r[Date] and [Date] <= r[#"Date +4 Days"]), type table),
    Ad_RunsLastFiveDays = Table.AddColumn(Ad_LastFiveDays, "# Of Runs Last Six Days", each List.Sum([Last Six Days][#"# Of Runs"]), Int64.Type),
    Ad_ToBeChecked = Table.AddColumn(Ad_RunsLastFiveDays, "To Be Checked", each if [#"# Of Runs Last Six Days"] > 0 then "Yes" else "No", type text),
    #"Sorted Rows" = Table.Sort(Ad_ToBeChecked,{{"Index", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Date", "Unique_ID_2", "# Of Runs", "To Be Checked"})
in
    #"Removed Other Columns"

[Ramh84]

Thank you so much for your brilliant solution. 

It worked perfectly to meet my expected results.

Have a great day ahead!

[Ramh84]

I hope that below example makes it more clear.

[Anonymous]

Hi @Ramh84 ,

Please try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEKTy0uAVKefkDCxNTI0igeJBIP5iNYBkqxOiBtRri0GWPVZgjVZoxLmwle20xwaTPFq80UlzYzvNrMcGkzx6vNHJc2C7xBYoFLmyUu22IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Job = _t, Type = _t, Unique = _t, Unique_ID_2 = _t, #"# Of Runs" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Job", type text}, {"Type", type text}, {"Unique", type text}, {"Unique_ID_2", type text}, {"# Of Runs", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Last4DaysValues", each List.Max(Table.SelectRows(#"Changed Type",(x)=>x[Unique_ID_2]=[Unique_ID_2] and x[Date]<[Date] and x[Date]>=Date.AddDays([Date],-4))[#"# Of Runs"] & {0})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "To Be Checked", each if [#"# Of Runs"]>0 or [Last4DaysValues]>0 then "Yes" else "No"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Last4DaysValues"})
in
    #"Removed Columns"

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Ramh84]

Thanks for your support. I will check it, and let you know the results soon.