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.