How to join 3 Records from an Array by _time then by _field.index number

[MOOREJEFFREY]

Hello All:

 I have a dataset with 3 values each for each array index number ( 0-20 ). Would like to join all three records into one rowset by "_time" then by "index number" ( example: _field._embedded_state.0..20 ). Any help is greatly appreciated. 

Thanks

Jeff

_time                            _value                                                                                                                       _field 12/8/2021 2:03:34 PM 110403 _embedded._state.1.code 12/8/2021 2:03:34 PM 110403 _embedded._state.12.code 12/8/2021 2:03:34 PM 110403 _embedded._state.14.code 12/8/2021 2:03:34 PM 110403 _embedded._state.16.code 12/8/2021 2:03:34 PM 110403 _embedded._state.18.code 12/8/2021 2:03:34 PM 110403 _embedded._state.3.code 12/8/2021 2:03:34 PM 110423 _embedded._state.8.code 12/8/2021 2:03:34 PM 110429 _embedded._state.10.code 12/8/2021 2:03:34 PM 111513 _embedded._state.6.code 12/8/2021 2:03:34 PM 111525 _embedded._state.7.code 12/8/2021 2:03:34 PM 111528 _embedded._state.4.code 12/8/2021 2:03:34 PM 111529 _embedded._state.0.code 12/8/2021 2:03:34 PM 111529 _embedded._state.11.code 12/8/2021 2:03:34 PM 111529 _embedded._state.13.code 12/8/2021 2:03:34 PM 111529 _embedded._state.15.code 12/8/2021 2:03:34 PM 111529 _embedded._state.17.code 12/8/2021 2:03:34 PM 111529 _embedded._state.19.code 12/8/2021 2:03:34 PM 111529 _embedded._state.2.code 12/8/2021 2:03:34 PM 111529 _embedded._state.9.code 12/8/2021 2:03:34 PM 111532 _embedded._state.5.code 12/8/2021 2:03:34 PM 2021-12-01 T 21:12:22 _embedded._state.0.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 01:37:01 _embedded._state.1.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 01:38:53 _embedded._state.2.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 01:39:01 _embedded._state.3.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 01:43:22 _embedded._state.4.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 02:17:08 _embedded._state.5.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 02:19:16 _embedded._state.6.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 02:32:18 _embedded._state.7.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 07:22:42 _embedded._state.8.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 07:26:30 _embedded._state.9.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 07:47:08 _embedded._state.10.tstamp 12/8/2021 2:03:34 PM 2021-12-02 T 07:51:33 _embedded._state.11.tstamp 12/8/2021 2:03:34 PM 2021-12-03 T 04:47:24 _embedded._state.12.tstamp 12/8/2021 2:03:34 PM 2021-12-03 T 04:56:04 _embedded._state.13.tstamp 12/8/2021 2:03:34 PM 2021-12-03 T 15:04:38 _embedded._state.14.tstamp 12/8/2021 2:03:34 PM 2021-12-03 T 15:07:19 _embedded._state.15.tstamp 12/8/2021 2:03:34 PM 2021-12-03 T 19:10:55 _embedded._state.16.tstamp 12/8/2021 2:03:34 PM 2021-12-03 T 19:15:35 _embedded._state.17.tstamp 12/8/2021 2:03:34 PM 2021-12-07 T 11:50:29 _embedded._state.18.tstamp 12/8/2021 2:03:34 PM 2021-12-07 T 11:55:11 _embedded._state.19.tstamp 12/8/2021 2:03:34 PM Beam has moved or calibration changed.Tool: SKSTask: T_ROB2 _embedded._state.4.desc 12/8/2021 2:03:34 PM Setup complete for:Tool: SKSTask: T_ROB2New work object: [[569.07,1667.26,760.47],[0.71,0,0,-0.7]]New TCP: [[92.05,0.91,397.04],[0.92,0.01,0.39,-0.05]]Max, Mean Deviation: 0.00, 0.00 _embedded._state.7.desc 12/8/2021 2:03:34 PM Task: T_ROB1/R1_FORD_KNUCKLE_OP105_AWD/W010_F1_AWD/ArcL/114Arc ignition supervision signal not set at start of welding. _embedded._state.14.desc 12/8/2021 2:03:34 PM Task: T_ROB1/R1_FORD_KNUCKLE_OP105_AWD/W010_F1_AWD/ArcL/114Arc ignition supervision signal not set at start of welding. _embedded._state.18.desc 12/8/2021 2:03:34 PM Task: T_ROB2/R2_FORD_KNUCKLE_OP105_AWD/W009_F1_AWD/ArcL/119Arc ignition supervision signal not set at start of welding. _embedded._state.1.desc 12/8/2021 2:03:34 PM Task: T_ROB2/R2_FORD_KNUCKLE_OP105_AWD/W009_F1_AWD/ArcL/119Arc ignition supervision signal not set at start of welding. _embedded._state.12.desc 12/8/2021 2:03:34 PM Task: T_ROB2/R2_FORD_KNUCKLE_OP105_AWD/W009_F1_AWD/ArcL/119Arc ignition supervision signal not set at start of welding. _embedded._state.3.desc 12/8/2021 2:03:34 PM Task: T_ROB2/R2_FORD_KNUCKLE_OP105_AWD/W009_F1_AWD/ArcLEnd/136Seam name: W009_F1_AWD_FLL.Time from weld start: 0:0. _embedded._state.10.desc 12/8/2021 2:03:34 PM Task: T_ROB2/R2_FORD_KNUCKLE_OP105_AWD/W069_F1_AWD/ArcLEnd/195Arc ignition supervision signal not set at start of welding. _embedded._state.16.desc 12/8/2021 2:03:34 PM Task: T_ROB2/R2_FORD_KNUCKLE_OP105_AWD/W071_F1_AWD/ArcLEnd/211Arc supervision signal went low during welding.Seam name: W071_F1_AWD.Time from weld start: 0:1.8. _embedded._state.8.desc 12/8/2021 2:03:34 PM The TCP for Tool, SKS, has been updated by BEUpdateTool.Task: T_ROB1New TCP: [[86.47,-1.85,399.75],[0.93,0,0.37,0.01]]Change: 0.175433Elapsed Time: 183.589 _embedded._state.19.desc 12/8/2021 2:03:34 PM The TCP for Tool, SKS, has been updated by BEUpdateTool.Task: T_ROB1New TCP: [[86.49,-1.98,399.86],[0.93,0,0.37,0.01]]Change: 0.4204Elapsed Time: 57.766 _embedded._state.15.desc 12/8/2021 2:03:34 PM The TCP for Tool, SKS, has been updated by BEUpdateTool.Task: T_ROB1New TCP: [[86.9,-1.93,399.87],[0.93,0,0.37,0.01]]Change: 0.411315Elapsed Time: 56.988 _embedded._state.0.desc 12/8/2021 2:03:34 PM The TCP for Tool, SKS, has been updated by BEUpdateTool.Task: T_ROB2New TCP: [[92,1.02,397.41],[0.92,0.01,0.39,-0.05]]Change: 0.226283Elapsed Time: 66.209 _embedded._state.11.desc 12/8/2021 2:03:34 PM The TCP for Tool, SKS, has been updated by BEUpdateTool.Task: T_ROB2New TCP: [[92.05,0.81,397.34],[0.92,0.01,0.39,-0.05]]Change: 0.31603Elapsed Time: 66.241 _embedded._state.9.desc 12/8/2021 2:03:34 PM The TCP for Tool, SKS, has been updated by BEUpdateTool.Task: T_ROB2New TCP: [[92.15,0.7,397.41],[0.92,0.01,0.39,-0.05]]Change: 0.355017Elapsed Time: 66.406 _embedded._state.13.desc 12/8/2021 2:03:34 PM The TCP for Tool, SKS, has been updated by BEUpdateTool.Task: T_ROB2New TCP: [[92.21,0.7,397.23],[0.92,0.01,0.39,-0.05]]Change: 0.188695Elapsed Time: 66.359 _embedded._state.17.desc 12/8/2021 2:03:34 PM The TCP for Tool, SKS, has been updated by BEUpdateTool.Task: T_ROB2New TCP: [[95.8,-6.43,396.82],[0.92,0.01,0.4,-0.05]]Change: 8.42594Elapsed Time: 67.299 _embedded._state.2.desc 12/8/2021 2:03:34 PM The tool, SKS, has been initialized in the BullsEye data collection.Task: T_ROB2 _embedded._state.6.desc 12/8/2021 2:03:34 PM The tool, SKS, is not set up.Task: T_ROB2 _embedded._state.5.desc 12/9/2021 1:44:40 AM 110403 _embedded._state.1.code 12/9/2021 1:44:40 AM 110403 _embedded._state.12.code 12/9/2021 1:44:40 AM 110403 _embedded._state.14.code 12/9/2021 1:44:40 AM 110403 _embedded._state.16.code 12/9/2021 1:44:40 AM 110403 _embedded._state.18.code 12/9/2021 1:44:40 AM 110403

[MOOREJEFFREY]

Thank you very much for the link. Looks like a great resource. I will get studying straight away. Also thank you again for the query help.

Best Regards

Jeff

[MOOREJEFFREY]

Thank you very much for the feedback. My M code skills are not yet very strong. I also find the documentation and examples to be very brief. Have you any recommendations for M code learning? Thank you again for your feedback. I will give it a try.

Best Regards

Jeff

[lbendlin]

My recommendation for learning M would be to internalize Ben Gribaudo's primer.

https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...

In my opinion that is all you will ever need.

[lbendlin · Accepted solution]

Here is one possible implementation

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VhdT+M6EP0rFs+p67FjO/Hb8vUCu6ygq31AVRUaL+RumlRtCnfvr7/jVLBQ7KbpskgIqU1LzvE5k/HMuNfXB8CHyZAzDgRiw8RBdADA4vZiYmc3Ns9tTifLJmssBTqtc3swjnrC+J64eE+c2hOX7IcTXTDuhXWuxlOvSrYdBxK8y3UEBSSXPpjuhCU+WMejQ5jXXKe3QEw6MjOI63h0QZzcE9cdTT8u3Q/XsfFCsM7VBPfBtgbFfRgAHzAgI8LBADfcy8JogxezeRcPRx4GRmjDwF+s+vEkRnr3De/Jkwb0iH48sQjEJ+7Dww1gfLw7VPbkSQ0of13pxSOQyqtH9+HRGBwTe+OT9ORRRjD/JujFE4fiDL0SWhuJOeRvT7tmtHBEsVPE40A/7kUklWF+ol1z2hGBRBbcZoFO349IYz4GCnMPIkxpZqS37cGuaf1IJI3wE+2a19oRgZHMBHrAron9SCQN+AtjR2Yf2mxG7rIlmdX3Nif1gkyzsrhZZE1RV2R6l1W3SDaq69KQq7OrUbb8achocnlxGKhXuV1OA2td2WY1J9N6Ni9tY8mPemG8xF/sA3moFz9JffOPnTaGXF9LlVKmI1BKU64irRiNNa7CqIaI4d8Ar8ZjhxwdfXWIlFMmI0ZTiESqKYvbu1OOXzGEUJE6DJPj8efs34h8tllFju190Ro3BP/FovbVX7622HxmBYaXMDm9uDyenH35dnR2fjK5+ApMTj59Px5+Z8Amp9Bef1pMz4cAMb6T4rYq2uAvV3O7uC+W7TV+m5WkqhuytA3J8K3JFg2pf5AHW+ZFdUsDW+2DCE12FMqHlzwslKUbQtM3F/pRdPIPIlT8BZ0nVT4Eoa5cbauymTXk2R2T0/NzOipmWIAW9azVtlaKm94wfzDZm4hUr0Wm8s0fvHoTrRo2tXIAp9Uj8cFWDSnrB5KvFijrSd6L+D8RBmMPOM35Z7xthu6sq/iumRDXTCLXTKK2o91YW5HVPEeOnNz8Iocn39oP7jb6vPY96xqJwr4SDVCKxLaRUi3XbUO4JkOFbrvHeHzU9kXXJUDLWIiTMpsvcRVnzRBIBJVJ6Gz3vmZSZyZNWjOJ6jATcxa/tCI11cp7EsDh612drI2ItRHdZQRAgNywghyJdyDdvrv/3Al/MZZEQBlvZ5IYgjPJbyucK55sJJhSlLPQbyPv6WU9YiXrEUuER6zfdgQo5nETe4fXv71ZNsyAM6N3fzRCSgb6lZmY+TfM9k735m44PLnhYgc3kCQqla/cCBn6cesd3UiaRAOMrNv+iiZ8w0686SbBUibTjVrmjg5p4JezDi+Nx0Phejaelf7DBYqKNHjb4aoslye/LEFDGR50yhKPL9goadexaXvPfimgWD7NBKt5J/NjlR7/Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [_time = _t, _value = _t, _field = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "_field", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"_field.1", "_field.2", "_field.3", "_field.4"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"_time", "_value", "_field.3", "_field.4"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"_field.3"}, {{"Group", each _, type table [_time=nullable text, _value=nullable text, _field.3=nullable text, _field.4=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Pivot", each Table.Pivot([Group], List.Distinct([Group][_field.4]), "_field.4", "_value")),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Added Custom", "Pivot", {"_time", "code", "tstamp", "desc"}, {"_time", "code", "tstamp", "desc"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Pivot",{"_field.3", "_time", "code", "tstamp", "desc"})
in
    #"Removed Other Columns1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".