Forum Discussion

Ben81's avatar
Ben81
Helper I
3 years ago
Solved

Find earliest value in column

Hi all,  I'm having some trouble finding a solution to this, any help? I have a table that looks something like below and all I want to do is add a flag of 1 from the earliest date until the fi...
  • m_alireza's avatar
    3 years ago

    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