Forum Discussion
babajat
4 years agoFrequent Visitor
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...
- 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.
lbendlin
Super User
4 years agoThat 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.