Forum Discussion
Solved
Count IF with mutliple ifs date issue
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 ...
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
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