Forum Discussion
Find earliest value in column
- 3 years ago
Ben81 ,
Yes, it is possible.
you can:
1. add column that finds the min date of when stage = completed2. add conditional column that compares the list of dates against the column created in step 1. If less than or equal to min date of stage = completed, 1, otherwise, 0.
3. delete column created in step 1 as no longer necessary.
You can copy the query below in your advanced editor and adjust as needed for your actual table:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLUN7DUNzIwMlLSUfLMUwgoyk8vSi0uVorViVYyNNA3MCFK0jk/tyAntSQ1hbA+oHV4JC2QDc3JL4aaaGCOT5MZPklTPJIovkfyQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Stage = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"Stage", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "mincompleteddate", each List.Min(Table.SelectRows(#"Changed Type",(x)=>x[Stage]="Completed")[DATE])), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "flag", each if [DATE] <= [mincompleteddate] then 1 else 0), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"mincompleteddate"}) in #"Removed Columns"
I also added a link to the sample pbix file for your reference:
https://drive.google.com/file/d/1AagVmdR8bB1Ghe5fkK0-wyEPdpO0QSjy/view?usp=sharing
Ben81 ,
Yes, it is possible.
you can:
1. add column that finds the min date of when stage = completed
2. add conditional column that compares the list of dates against the column created in step 1. If less than or equal to min date of stage = completed, 1, otherwise, 0.
3. delete column created in step 1 as no longer necessary.
You can copy the query below in your advanced editor and adjust as needed for your actual table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLUN7DUNzIwMlLSUfLMUwgoyk8vSi0uVorViVYyNNA3MCFK0jk/tyAntSQ1hbA+oHV4JC2QDc3JL4aaaGCOT5MZPklTPJIovkfyQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Stage = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"Stage", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "mincompleteddate", each List.Min(Table.SelectRows(#"Changed Type",(x)=>x[Stage]="Completed")[DATE])),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "flag", each if [DATE] <= [mincompleteddate] then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"mincompleteddate"})
in
#"Removed Columns"
I also added a link to the sample pbix file for your reference:
https://drive.google.com/file/d/1AagVmdR8bB1Ghe5fkK0-wyEPdpO0QSjy/view?usp=sharing