Dyota's blog

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

#dynamicsf&o #powerquery