Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to 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"
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"
Thank you so much for your brilliant solution.
It worked perfectly to meet my expected results.
Have a great day ahead!
I hope that below example makes it more clear.
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
Thanks for your support. I will check it, and let you know the results soon.