We’ve got some equipment that we use to measure strengths of people exercising, but it’s software is pretty old & missing some functionality so I want to extract some data from their database
It’s a Microsoft Access MDB file (Versions 2.x, 3.x and 4.x). In Version 5, the application was extended to support ODBC database
The particular row I want to extract is the actual information recorded by the dynamometer (at 100Hz): “Data is collected from the dynamometer at a rate of 100 samples per second. When a test is completed, the application stores torque, position and velocity values as a BLOB structure in the appropriate row of the dataset (see Dataset table).”
The first 5 rows of the DataSet table are defined as follows (the 5th row, BLOB, is the one I’m interested in extracting as a table):
Field Name Data Type Description
[Record #] [int] Record number
[PATID] [float] Patient ID
[STUDYID] [float] Study ID
[DATAID] [float] Dataset ID
[BLOB] [image] dynamometer data
…
Where
BLOB structure:
struct StDataPoint
{
short f_nTorque; // in foot-lbs * 10
short f_nPosition; // in degrees * 10
short f_nVelocity; // in degrees / second * 10
};
Pulling the BLOBs out using PowerQuery was easy enough, but converting them proved to be a bit of a kludge, and I can’t help but feel there must be a better way.
My steps were: 1. Convert the BLOB to a binary with: Binary.ToText([BLOB]as binary, 1) as text), then
2. Split the resulting column into 4 character chunks (each short byte)
3. Since there are 3 values, I added an index column, then Mod(3) and a conditional column to name each row (in my case: Position, Torque, and Velocity)
4. Now I can convert the text back to a Binary Binary.FromText([Count.Custom],1)), which then allows me to convert the Binary to a List: each Binary.ToList([Binary]) as list), so now I can transform this list by combining pairs of rows, and finally convert this text list to an integer.
Here’s the M-Code:
let
Source = Access.Database(File.Contents("F:\ODF\QSL Screening\biodexfemale.mdb"), [CreateNavigationProperties=true]),
_DataSet = Source{[Schema="",Item="DataSet"]}[Data],
// Just 3 arbitrary rows (Record #'s 16,17, and 18) for this Test
#"Filtered Rows" = Table.SelectRows(_DataSet, each ([#"Record #"] = 16 or [#"Record #"] = 17 or [#"Record #"] = 18)),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Record #", "BLOB"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Binary.ToText([BLOB]as binary, 1) as text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BLOB"}),
#"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Custom", Splitter.SplitTextByRepeatedLengths(4), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Custom", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Mod 3 To name rows" = Table.AddColumn(#"Added Index", "Custom.1", each Number.Mod([Index],3)),
#"Name Rows" = Table.AddColumn(#"Mod 3 To name rows", "Custom.2", each if [Custom.1] = 1 then "Torque" else if [Custom.1] = 2 then "Position" else if [Custom.1] = 0 then "Velocity" else null),
#"Removed Columns1" = Table.RemoveColumns(#"Name Rows",{"Index", "Custom.1"}),
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Record #", "Custom.2"}, {{"Count", each _, type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Custom"}, {"Count.Custom"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Count", each true),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Custom.2", "Channel"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Count.Custom", type text}}),
#"Added Index1" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index1",{"Index", "Record #", "Channel", "Count.Custom"}),
#"Binary From Text" = Table.AddColumn(#"Reordered Columns", "Binary", each Binary.FromText([Count.Custom],1)),
#"Binary to List" = Table.AddColumn(#"Binary From Text", "List", each Binary.ToList([Binary]) as list),
#"Combine List values" = Table.TransformColumns(#"Binary to List", {"List", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Convert text to numeric" = Table.TransformColumnTypes(#"Combine List values",{{"List", Int64.Type}}),
#"Removed Columns2" = Table.RemoveColumns(#"Convert text to numeric",{"Count.Custom", "Binary"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"List", "Value"}})
in
#"Renamed Columns1"