Parsing database logs from Dynamics F&O
parseDatabaseLogUpdate = (updateText as text) as table =>
let
// define what the delimiter character is
delim = "ÿþ",
split = List.Transform(
Text.Split(updateText, delim),
Text.Trim
),
#"Converted to Table" = Table.FromList(split, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
index = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type),
// the changes happen in sets of three. This adds an "batch index" so that the rows can be bathed up into sets of three.
addBatchIndex = Table.TransformColumns(
index,
{
{
"Index",
each Number.IntegerDivide(_, 3),
type number
}
}
),
groupByBatch = Table.Group(
addBatchIndex,
{"Index"},
{{"Table", each [Column1], type list}}
),
// the first thing in the batch is the field name; the second thing is the original value; the third thing is the changed value
createRecord = Table.TransformColumns(
groupByBatch,
{
{
"Table",
each [
Field = _{0}?,
Value1 = _{1}?,
Value2 = _{2}?
]
}
}
),
#"Expanded Table" = Table.ExpandRecordColumn(
createRecord,
"Table",
{"Field", "Value1", "Value2"},
{"Field", "Value1", "Value2"}
),
// only interested in records where there is a change from Value1 to Value2
changesOnly = Table.SelectRows(
#"Expanded Table",
each ([Value1] <> [Value2])
),
cleanUpCommas = Table.ReplaceValue(changesOnly,",","",Replacer.ReplaceText,{"Field"})
in
cleanUpCommas