Forum Discussion
DAX for complex date extraction/count
Hello,
Is it possible to have an extraction and/or a count of the dates from this column A in the attached screenshot below, everything replicated in PowerBi? Two desired results are shown, preferably #2.
a) purpose is to extract/count all the dates in the chronological order (in which they are listed anyway in each cell) only for 2022.
b) There is no clear standard form therefor Power Querry's "Split" cannot be used. Sometimes "_" is the link and pattern, but sometimes there is no "_" between text and the next date.
c) date is always in the dd/mm/yyyy format.
Thank you
2 Replies
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkmtKDEw1AciIwMj4/jc/KJUkFC8gYm+gSlIzEgBJKYAElSK1YlWKs7PhaowgusC8WEYKGZoABZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextWithDates = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextWithDates", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "DatesList", each List.Transform(List.Split(Text.ToList(Text.Select([TextWithDates], {"0".."9","/"})), 10), each Text.Combine(_, ""))), #"Expanded DatesList" = Table.ExpandListColumn(#"Added Custom", "DatesList"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded DatesList",{{"DatesList", type date}}) in #"Changed Type1"Once you have the data in this structure, you can just make a matrix visual with Date on columns with count aggregation to get your preferred visual.
Pat
