Count IF with mutliple ifs date issue

[PawelPowerBI]

hi I am dealing with a report that is reviewed & updated by multiple users.

The report is in a database. Whenever a row is updated an edit date is added.

I am looking to create a dashboard that presents timeline and the number of edits for a given day.

My idea so far was to extract the date of each seperate update into a column. And i am able to do seperate pivots for each edit 

but I am unable to get it to what i want to see. 

Edit1 = IF(Data[Edit #*]=1,Data[Edit Date*],"ignore")

Edit2a = if(Data[Edit #*]=2,left(Data[Edit Date*],11),"ignore")

Edit2b = if(Data[Edit #*]=2,RIGHT(Data[Edit Date*],11),"ignore")

EDIT3a = if(Data[Edit #*]=3,left(Data[Edit Date*],11),"ignore")

EDIT3b = if(Data[Edit #*]=3,right(left(Data[Edit Date*],25),11),"ignore")

EDIT3c = if(Data[Edit #*]=3,RIGHT(Data[Edit Date*],11),"ignore")

[Anonymous · Accepted solution]

Hi @PawelPowerBI,

I'd like to suggest you expand these date value strings and add an index as 'edit' step, then you can simply use these value to create graphs and analysis.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUYo2MjAy1jXRNTSJVYCzzWKVYnWilQyA8mCGIZJCI6ikMVzMUNfQAKbZSNcQyRygylgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Edit = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Edit", Int64.Type}, {"Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddIndexColumn(Table.FromList(List.Select(Text.Split([Date]," "),each _ <> "")),"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Index"}, {"SubDate", "Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom","[","",Replacer.ReplaceText,{"SubDate"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"SubDate"})
in
    #"Replaced Value1"

Regards,

Xiaoxin Sheng