none
Add a grouped index column - seems way too difficult RRS feed

  • Question

  • In a query, I have a resultant table that's quite large, and is shaped as follows:

    ...

    What I'd like to end up with is a clustered index column (called "time" in this example) incrementing on every row as follows:

    My first attempt added a custom index table, but the while this appears to work in the Power Query editor OK, performance on this is extraordinarily slow to load - approximately 20 seconds per row, and I will have 10's of thousands of rows to load which will grow with time. Here's that (slow loading) code:

    let
        Source = Access.Database(File.Contents("F:\ODF\QSL Screening\biodexfemale.mdb"), [CreateNavigationProperties=true]),
        _DataSet = Source{[Schema="",Item="DataSet"]}[Data],
        Filter_Just_One_Athlete = Table.SelectRows(_DataSet, each ([PATID] = 1078) and ([Anatom_ref] = 90)),
        Removed_Other_Columns = Table.RemoveColumns(Filter_Just_One_Athlete,{"Number Spare 6", "Number Spare 7", "Number Spare 8", "Number Spare 9", "Number Spare 10", "Number Spare 11", "Number Spare 12", "Number Spare 13", "Number Spare 14", "Number Spare 15", "PatientSet", "StudySet", "Stop Position1", "Stop Position2", "Stop Position3", "Min Position", "Max Position", "Text Spare 1", "Text Spare 2", "Text Spare 3", "Text Spare 4", "Text Spare 5", "Text Spare 6", "Text Spare 7", "Text Spare 8", "Text Spare 9", "Text Spare 10", "Results", "TORQAWY", "TORQTWD", "SAFETYSPDAWY", "SAFETYSPDTWD", "PASVAWY", "PASVTWD", "PATID", "STUDYID", "DATAID", "Anatom_ref", "Anatom_Mess", "Limb Weight", "SPDAWY", "SPDTWD", "PASVTRQLMTAWY", "PASVTRQLMTTWD", "TOTROM", "Max Rep Work Awy", "Max Rep Work Twd", "Total Work Awy", "Total Work Twd", "Number Spare 1", "Number Spare 2", "Number Spare 3", "Number Spare 4", "Number Spare 5", "PK Torq Awy", "PK Torq Twd"}),
        #"Invoked Custom Function" = Table.AddColumn(Removed_Other_Columns, "fXBinToTbl", each fXBinToTbl([BLOB])),
        #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"BLOB"}),
        #"Added Custom" = Table.AddColumn(#"Removed Columns", "IndexTable", each Table.AddIndexColumn([fXBinToTbl], "Index", 1, 1)),
        #"Expanded fXBinToTbl" = Table.ExpandTableColumn(#"Added Custom", "fXBinToTbl", {"f_nTorque", "f_nPosition", "f_nVelocity"}, {"fXBinToTbl.f_nTorque", "fXBinToTbl.f_nPosition", "fXBinToTbl.f_nVelocity"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Expanded fXBinToTbl", "IndexTable", {"Index"}, {"Custom.Index"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"fXBinToTbl.f_nTorque", Int64.Type}, {"fXBinToTbl.f_nPosition", Int64.Type}, {"fXBinToTbl.f_nVelocity", Int64.Type}, {"Custom.Index", Int64.Type}})
    in
        #"Changed Type"

    The query to get to the first image posted above is as follows:

    let
        Source = Access.Database(File.Contents("F:\ODF\QSL Screening\biodexfemale.mdb"), [CreateNavigationProperties=true]),
        _DataSet = Source{[Schema="",Item="DataSet"]}[Data],
        Filter_Just_One_Athlete = Table.SelectRows(_DataSet, each ([PATID] = 1078) and ([Anatom_ref] = 90)),
        #"Renamed Columns" = Table.RenameColumns(Filter_Just_One_Athlete,{{"Record #", "Record#"}}),
        Removed_Other_Columns = Table.RemoveColumns(#"Renamed Columns",{"Number Spare 6", "Number Spare 7", "Number Spare 8", "Number Spare 9", "Number Spare 10", "Number Spare 11", "Number Spare 12", "Number Spare 13", "Number Spare 14", "Number Spare 15", "PatientSet", "StudySet", "Stop Position1", "Stop Position2", "Stop Position3", "Min Position", "Max Position", "Text Spare 1", "Text Spare 2", "Text Spare 3", "Text Spare 4", "Text Spare 5", "Text Spare 6", "Text Spare 7", "Text Spare 8", "Text Spare 9", "Text Spare 10", "Results", "TORQAWY", "TORQTWD", "SAFETYSPDAWY", "SAFETYSPDTWD", "PASVAWY", "PASVTWD", "PATID", "STUDYID", "DATAID", "Anatom_ref", "Anatom_Mess", "Limb Weight", "SPDAWY", "SPDTWD", "PASVTRQLMTAWY", "PASVTRQLMTTWD", "TOTROM", "Max Rep Work Awy", "Max Rep Work Twd", "Total Work Awy", "Total Work Twd", "Number Spare 1", "Number Spare 2", "Number Spare 3", "Number Spare 4", "Number Spare 5", "PK Torq Awy", "PK Torq Twd"}),
        #"Invoked Custom Function" = Table.AddColumn(Removed_Other_Columns, "fXBinToTbl", each fXBinToTbl([BLOB])),
        Removed_Columns = Table.RemoveColumns(#"Invoked Custom Function",{"BLOB"}),
        #"Expanded fXBinToTbl" = Table.ExpandTableColumn(Removed_Columns, "fXBinToTbl", {"f_nTorque", "f_nPosition", "f_nVelocity"}, {"fXBinToTbl.f_nTorque", "fXBinToTbl.f_nPosition", "fXBinToTbl.f_nVelocity"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded fXBinToTbl",{{"fXBinToTbl.f_nTorque", Int64.Type}, {"fXBinToTbl.f_nPosition", Int64.Type}, {"fXBinToTbl.f_nVelocity", Int64.Type}, {"Record#", type text}}),
        #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"fXBinToTbl.f_nTorque", "Torque"}, {"fXBinToTbl.f_nPosition", "Position"}, {"fXBinToTbl.f_nVelocity", "Velocity"}})
    in
        #"Renamed Columns1"

    Can anyone please point me the right direction for a method to create this clustered index column, or should I resort to doing this in DAX?

    Thanks in advance for any help

    rod

    Thursday, August 30, 2018 3:53 PM

Answers

  • You should chnage fxBinToTbl function to this below

    fxBinToTbl = (b as binary) =>
            let
    
                parser = BinaryFormat.ByteOrder(
                    BinaryFormat.Record([
                        Torque = BinaryFormat.SignedInteger16,
                        Position = BinaryFormat.SignedInteger16,
                        Velocity = BinaryFormat.SignedInteger16]),
                    ByteOrder.LittleEndian),
    
                ToTable = Table.FromColumns({Binary.ToList(b)}),
                #"Added Index" = Table.AddIndexColumn(ToTable, "Index", 0, 1),
                #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 6), Int64.Type}}),
                #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"LST", each parser(#binary(_[Column1])), type record}}),
                #"Expanded {0}" = Table.ExpandRecordColumn(#"Grouped Rows", "LST", {"Torque", "Position", "Velocity"}, {"Torque", "Position", "Velocity"}),
                #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Index"}),
                AddIdxColumn = Table.AddIndexColumn(#"Removed Columns", "Time", 1, 1)
            in
                AddIdxColumn,

    • Marked as answer by rodwhiteley Friday, August 31, 2018 8:23 AM
    Thursday, August 30, 2018 5:42 PM

All replies

  • You should chnage fxBinToTbl function to this below

    fxBinToTbl = (b as binary) =>
            let
    
                parser = BinaryFormat.ByteOrder(
                    BinaryFormat.Record([
                        Torque = BinaryFormat.SignedInteger16,
                        Position = BinaryFormat.SignedInteger16,
                        Velocity = BinaryFormat.SignedInteger16]),
                    ByteOrder.LittleEndian),
    
                ToTable = Table.FromColumns({Binary.ToList(b)}),
                #"Added Index" = Table.AddIndexColumn(ToTable, "Index", 0, 1),
                #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 6), Int64.Type}}),
                #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"LST", each parser(#binary(_[Column1])), type record}}),
                #"Expanded {0}" = Table.ExpandRecordColumn(#"Grouped Rows", "LST", {"Torque", "Position", "Velocity"}, {"Torque", "Position", "Velocity"}),
                #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Index"}),
                AddIdxColumn = Table.AddIndexColumn(#"Removed Columns", "Time", 1, 1)
            in
                AddIdxColumn,

    • Marked as answer by rodwhiteley Friday, August 31, 2018 8:23 AM
    Thursday, August 30, 2018 5:42 PM
  • Bardzo ci dziękuje za pomoc!

    @BillSzysz - you are a life-saver! Worked perfectly.

    Just for those who may be stumbling on this problem to help their own work, in the code snip above I had forgotten to include the code for the function "fxBinToTbl" (which @Bill Szysz also wrote) and the main query references. Bill's addition to this - the final line:

     AddIdxColumn = Table.AddIndexColumn(#"Removed Columns", "Time", 1, 1)

    works correctly, and importantly quickly, adding the index column exactly as required

    Friday, August 31, 2018 8:30 AM
  • Glad to help you
    Your Polish is much better than my English

    Cheers :-)

    Friday, August 31, 2018 9:33 AM
  • How can something change its velocity
    but not change its position?
    Friday, August 31, 2018 8:12 PM
  • Probably rounding errors, or noise.

    The output from the device is likely a voltage that gets converted.

    These numbers are supposed to be position (degrees * 10) and velocity (degrees/sec * 10).

    I am guessing that these apparent anomalies could be caused by noise, or rounding errors, or perhaps the device simply isn't as accurate as it claims to be.

    In practice, it's not such a big deal as accuracy to within about 2 or 3 degrees and about 10 degrees/second is good enough for most everything we need to do.

    Saturday, September 1, 2018 1:19 PM
  • Excel 2010
    If position and velocity can be measured accurately,
    then time can be calculated with some simple formulas.
    I added some fictitious decimal places to the "measured" data,
    found the shape of the curves and got the time with Solver.
    http://www.mediafire.com/file/n01gn1pplreq3ib/08_30_18.xlsx/file
    http://www.mediafire.com/file/9xaxmzms8y8yk49/08_30_18.pdf/file

    Saturday, September 1, 2018 4:14 PM