Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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, prefe...
General Comment
Need Help
Tips and Tricks
ppm1's avatar
ppm1
Solution Sage
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

    Can you please guide me step by step?

     

    So I have my above attached screenshot as xlsx. I load it into PowerBi. What should I do next in order for me to get a visual in Pbi similar to my "Desired result" from Excel?