Forum Discussion
Flatten audit table
Hi, gban
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may paste the following m codes in 'Advanced Editor' to apply transformations to the query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIAYuf8vLTMotzUFCA7pKg0FUEZGRgZ6Boa6RoYKcXqRCsZAYVAmgJS81Iy89IR6twSc4pR1BuC1RtjqIcpRNVgqGtsANZgQrQGI0ul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, original_id = _t, #"status_1 " = _t, flag_1 = _t, flag_2 = _t, update_date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"original_id", Int64.Type}, {"status_1 ", type text}, {"flag_1", type logical}, {"flag_2", type logical}, {"update_date", type date}}), Custom1 = Table.SelectRows(#"Changed Type",each [original_id]=0), #"Added Custom" = Table.AddColumn(Custom1, "Custom", each let l = List.Sort( Table.SelectRows(#"Changed Type",each [original_id]<>0)[update_date],Order.Ascending) in Record.FromList( l, List.Transform( List.Transform(l,each List.Count( List.Select(l,(x)=>x<_))+1) , (y)=> "flag_"&Text.From(y)&"_true_date") )), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"flag_1_true_date", "flag_2_true_date", "flag_3_true_date"}, {"flag_1_true_date", "flag_2_true_date", "flag_3_true_date"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"original_id", "status_1 ", "flag_1", "flag_2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"update_date", "confirm_date"}}) in #"Renamed Columns"Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Looks like a Slowly Changing Dimension.
You can read about the theory by web search if you like. (different types, pros/cons)
Your data scenario looks a little more complicated with all the different flags to represent the state of an object.
Does the original id = 0 record represent the complete state of an object? ->
and Is a id =0 record generated every day for every object?
Are you only interested in reporting on the id = 0 record?
Thanks, I'll read about slowly changing dimension.
Meanwhile I'll try to answer questions to see if that might lead to some other suggestions.
Yes, original id = 0 represents the complete state of an object.
No, rows are not generated every day for every object, new row is created only when user creates new entity or updates of existing entity. Now when I think about it probably original_id is a misleading column name, current_id would be better one - I don't know implementation details, but I guess it following happens when user updates a row:
1) current row values are copyed to new row
2) updated values are applied on new row
3) old row original id (which I would name current id) is updated to point to new row.
Which would give following:
1) There might be no history records for a row (if it was never updated)
2) There might be more than 1 history records during 1 day (if row was updated multiple times during the day), in such case only latest state during that day would be interesting. It is not likely, that state would change let's say from Pending to Confirmed and back to Pending, even if it was, I don't care about those exceptions. You can see it as Sales Order - Pending, Confirmed, Shipped - it might go back to Pending sometimes, but that's anomaly I'm fine to ignore.
So given the Sales order analogy - I need to know how many Pending orders were each day, how many Confirmed orders were each day, how many Shipped orders - so that I would see if there's any bottleneck in specfic state and correlations with other flags/parameters. So answering your last question - ideally I would like to turn historic records into additional columns for original id = 0 row, that would make visualization easy.