Forum Discussion

babajat's avatar
babajat
Frequent Visitor
4 years ago
Solved

Find if multiple values are occuring before other columns

Hello again!   I have this dataset with a series of interventions and a date for each intervention: INDX INT 1 DATE INT 1 INT 2 DATE INT 2 INT 3 DATE INT 3 INT 4 DATE INT 4 INT 5 DATE...
  • lbendlin's avatar
    4 years ago

    That source data is in rather bad shape.  First step is to unpivot it into something usable

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLLCoMwEEV/pWQt5GESzdIuRERQSxVF/P/faKbGIQ8tXQTiwZzJ3Mm2EU4y8rKLaco4FUwI+/EGUFImTzABkNQyB3oAnDJzgsE58MiyAFFo3bONAB/t4kVczK/+dG4Ej8sFwtzuus4V0mFp4Rna1jWAZF3jf1K7RJd/cppiMoad2pagJ85+ypXdNU2sqmsgxsuxquLA5tnVM/d2DSMZnAtPHlHpJIbcI3gnFcbp3/IgRTDbAm/v2/r++qz6a7zl5aAOg/9AvhkFde+dBp0qeSA8SUYn6cn73PcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INDX = _t, #"INT 1" = _t, #"DATE INT 1" = _t, #"INT 2" = _t, #"DATE INT 2" = _t, #"INT 3" = _t, #"DATE INT 3" = _t, #"INT 4" = _t, #"DATE INT 4" = _t, #"INT 5" = _t, #"DATE INT 5" = _t, #"INT 6" = _t, #"DATE INT 6" = _t]),
        CT = (List.Count(Table.ColumnNames(Source))-1)/2,
        #"Merged Columns1" = List.Accumulate({1..CT},Source,(state,current)=>Table.CombineColumns(state,{"INT " & Text.From(current), "DATE INT " & Text.From(current)},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged " & Text.From(current))),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"INDX"}, "Attribute", "Value"),
        #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " , ")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"INT", "Date"}),
        #"Changed Type with Locale" = Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Date", type date}}, "en-GB"),
        #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"INDX", Int64.Type}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"INDX", "INT", "Date"})
    in
        #"Removed Other Columns"

    After that you can create a measure that implements your desired logic

    XX or YY is first Int = 
    var a = min('Table'[Date])
    var i = CALCULATE(min('Table'[INT]),'Table'[Date]=a)
    return if (i in {"XX","YY"},1,0)

     

    see attached.

     

    Thank you for the challenge - the unpivoting part was tricky as it needed pairs of columns.