Forum Discussion
Anonymous
3 years agoNot applicable
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...
ppm1
3 years agoSolution Sage
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
3 years agoNot applicable
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?