Forum Discussion
Transform some Audit trail data from JIRA
Hello, I have a question on how to transform an "audit" table to make it easier to utilize...
1) We have a table of "Issues" and their current values.
2) We have an audit table that shows the changes in the value of the field "Team"
3) we want to transform that table in step 3 into the below.
How can we transform the table in step 2 into the table in step 3..
this data is coming from our JIRA system via a REST api call to get the "changelog" for each Jira Issue.
Here is one way to do it, demonstrated with a similar set of data. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJTcwFUoYgrG+ob2RgZABkQiUMlWJ1UJQZgbC+MUwZRA2EjgCrdUKoNUZVi0uZCViZKbKREVDaWSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Bug = _t, Field = _t, #"Change ID" = _t, #"Change Date" = _t, From = _t, To = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bug", type text}, {"Field", type text}, {"Change ID", Int64.Type}, {"Change Date", type date}, {"From", type text}, {"To", type text}}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Bug", Order.Ascending}, {"Change Date", Order.Ascending}})), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Bug] = #"Added Index"{[Index] + 1}[Bug] and [Field] = #"Added Index"{[Index] + 1}[Field] then #"Added Index"{[Index] + 1}[Change Date] else null, type date), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"From", "Index"}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Removed Columns", {{"Custom", null}}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Errors",{{"To", "Value"}, {"Change Date", "Date From"}, {"Custom", "Date To"}}) in #"Renamed Columns"Pat
1 Reply
Here is one way to do it, demonstrated with a similar set of data. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJTcwFUoYgrG+ob2RgZABkQiUMlWJ1UJQZgbC+MUwZRA2EjgCrdUKoNUZVi0uZCViZKbKREVDaWSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Bug = _t, Field = _t, #"Change ID" = _t, #"Change Date" = _t, From = _t, To = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bug", type text}, {"Field", type text}, {"Change ID", Int64.Type}, {"Change Date", type date}, {"From", type text}, {"To", type text}}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Bug", Order.Ascending}, {"Change Date", Order.Ascending}})), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Bug] = #"Added Index"{[Index] + 1}[Bug] and [Field] = #"Added Index"{[Index] + 1}[Field] then #"Added Index"{[Index] + 1}[Change Date] else null, type date), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"From", "Index"}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Removed Columns", {{"Custom", null}}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Errors",{{"To", "Value"}, {"Change Date", "Date From"}, {"Custom", "Date To"}}) in #"Renamed Columns"Pat