Accessing Binary Columns from Oracle Database

Super User

2 years ago

Please study the following code. I have converted the expected result into the original binary form and then back. You only need to use the code starting from "START HERE".

let
    Source = Json.Document( Text.FromBinary( Binary.Decompress( Binary.FromText( "H4sIAAAAAAAEAJXdTa4dMW5A4b143A3c+lFVKdMsI8gO0p1RRkH2nttAgOSdr2A5Qxum60oUKfKIov7lP3/987//23/87e/br3/69fn1l//50/7901/v+3//4vjHX1zH8eu//vJ/JbafEtezrST2fGNcPyQ++edHPjDGb//5mf/9/Pz2n4/873PPz99nJK5+4KxEB3z/fgQvU/T8lDifKxLbiMTMr7oiMb4/M4qLrq/9Xn1k236vur+O+6kI2u7gt7si1fg1O8P8sGr9PirCD4vm709FbpZ6Vb9V9Z+rIvdK5GT4Uf6z1wbVS7W/VZX8sL2WvrOM94rU1G++0rHs1f4TkTH5YdX+pwtm70reo/3xPMuxVPv71h9Wu9+rfYffNbZX+zfOkbFE+/fEKhGJ9sc9O5bq5aj2dXvVy4H2n36ly/LYK9KVjKs8qv1ZVeIrj2j/Gp3kDyLR/vPhh3XBHNX+hfa7YI76/U9/2IZItX8srfKI9tmAx9MFc1b72MtRVZ71/Eetkkk+q33cOPZyon08f2fsrOc/8JadsbO2P7u7DsZydSzYC2Op7Y+KbDWxs9r/dI1hlWc9/2T4tf3Rff+sKke/MgjyCAu3ilT7H1ZyVTmqfaIehj+qfTc+ho/nJyCpKke1PzExRKp9git/WLV/EcPUkMcq6lMvV21/67IkILlq+1v9mCLVPm4cVV5HFz9OiUi8nv/pjH06Y9dS+zszVs9fVY5ZD3PV8+/Yfj3MVe3/gUi1f3TGto7lru27I1cvd7VfvYyHr1T7pAnM2I3tr7MqYv5uFqQ8N9neMrq4a/t7v0IId6N94jFEGvN/qv2jievdfR83vtcqH2wfd1G9PNH+cxCOdsE8jflnf9jJV2r7QISzAclT29f18ZXa/k36Vg/zEPMv47Gnnv8gHEUk2r+OrjE28ae2v9f1ndXLhO1ExEBxdt/HKRH2zGqfqG/UXcza/sbwa5Wztu8mzvBr+8++HP7K8xvDTLTfZYn2Z7VPcMWWNKv9Gz4Eu/jU9Rtbg0g+xH1EZHVL26cLoJn1C/H4QHzwZX6HwJ8QQ+JVD3CdXQP+tm4Ao2Zzg2M+UJ9RmS6c7UPqh9/wt2UdPDAsto2t2M9NQFII92PtnIwH8Md4Buut5O/uNjimMl0Ho+bzqflsZX8XuJCkcQP+je7qj3OQdfAADYjPt+I/bMFkY4P/wTM+rFEAoAxElrtmQKzRIsBnI0Nl3mCAoDZc4lYIqBtlR9xKAa+bbcTv1B/c7COOpxHhya7gXBMUQFzUKVEBVFPYjj9gjTKessCbNQo93QoD75NsRZn6g64dmeMGDvyDvaQ88P4Q5PudroPujeNyPD/XwSbaxu2ECJ7E3y8iP1eBG7AnLiGCjP9l3YQIPiXI5nhbiCCHiy+rJkTw8XTRr/xcAA+AQ5sOEXzO9XYQIvhc/DC/cmf4ICGPtX5q/56kH+zVIYIPGQup1BYiOInySdi2EMGHUz2DlRDBuROz4p9DBB9hjSLRPl5zYP0hgoq4YEIEH2ZMVYYIPucyLdxCBJ9GXWM6lmifgAO4uYUIzsY112CNhQhOwk5VGSI4iRygSFuIoFZpEHBV++SrnunW9tfhSYjgxMNAXrYQwYdEiuOTLURw6sbVS7R/rj3MXe3jLVljIYI3gdbLKXi0j17MV0MEZ23/JQwOEZxsry7+EMGnZF+CvIUIPhw4AQS3EMEHKKCDvWv7OFj1MjN8kkF+2FPtd0d++GEhgs+zDpVCBPFjLyYWIvj04HxMbD9E8AEHu5JDBCeb+KXIat8//WHZ9wUVTnK0D9x0ew0RJM16QRshgg/nOoY9s/v+EghuIYKTChiHHyL4DDYLFkyI4INTco2FCM6etlqbsoUIGo7qx0IEZ8fyEl2ECOou3PjmKuo7LAD6/H7GxqRwLzxwbkBnalM+tX02cUpzPgvtu1fugYEP+bVFM2GBD2ftLyI/tf8npTmfRn1LULsHBE63pDqlPRyQQxpx4x4M+GLIjCUU8OHIwR8WCGgAj0/ewwCfnoLrx/YgwIm3fPlKbJ/hH9RyBQBOCxr8SqO+tSqD/x5LczDkrVEfwRXaD/zDKmUXe9jfJLYk6N2D/maTkRcPE/I3P5zq+ZVGfWjfsTTq6w97/GHRPvvLy4zF87PxWfy3r/Z9V3KY3yTs0Vse1T5bkkWZjfpY/I369gC/CSIgVNjD+x6KmYiT9+C+yWnGy1eqfaILZyzaLyc2e93D+l6giiLx/GxJ1teG9alKDj/2sD4TXg25rI8IFgq3n9X+svxrL+uzoAG9nNU+51IOP9oH23LEtIf1qcqXH7a0fewlrO/xXoF1z933wVBMclifMQy1j3tY32zC+zJjYX0PhVnQ0b2sj2MIXV9Y30SV3EbYRzM+DjusFG/Gtyxm2sP6JCRU8O9hfRM86MYX1jd7wPpWwp58Xw7DJIf1bR+nTJkYv8cJLOWruGedJwT2YZYvignsmzvuklku7KM258RgCvu886FiYvxsllD7PbBvUvbuIivs08bQy92wr1/Rjwf2Pa3+e1nKd0E/Iv6waN+6b78yspQpSTBYKO3zooQitf5l/dse2veSjjj+qJ9cHHizh/Z50HEylqekf8nh9qekf62Y0r71HbE9tG+iFwOf0L7Hu41+pb6f4FqR7PzkSVry04yfNYaHLe2zEAPtz7Je9mSi/tI+gmuA+l7axwUWl2Vo34sILqa0r5Ps4cA+G/ctS2T20j4RkV+J66f4gKKnPbTPkEQ/Hto3Sd+JyI7QvnmxYLiLVNp3svPVKR2fxSnfyyWp0j5wF+zqKO3Dj5GMHh9cP/6Ci1XBfZxB6GKO4r5ed3wTifoNFrgQXdzXCiTX5bEtAz/uyG2Fvcvi12Nr0rc8GD2C++a6Kugo7jvY+VD/VthLfOGM9ZAXs3Qscf0lhONC+1u13+SCA7gjuO+2JhezDO6bvVFsIcEB7lte3juK+wCk5LwHuI815lhi/F4OdywL4OMRzBHct31QP1XMR3lfi4tNrY/wvts82a/E9xNdaslHw/71/eCjWR+USP0fTfqZZn/Z0WleXvY+jp70cQKLyUD8sGWczFHnv7ayED/P4Di2O44yH5SJyYT4fXeyxj4Xminy08uwAIL8XognU9byPo76HMzZrJ9ZViTqhxHryov84HcvIgW+S7J2FPlx1kXl2XE29AN6+JV4f8IlfVnL+7xdhMG0vA/czwnBUeRnjYu38KN9alxevtLDHtg9k9zyvvWZ/TF61EfDFmes2q+PvdVLjX9ZFnO0vM9zO2YsyI9MUXx3tLyPjcyGCiV+iLjGWt5H3qMfL/FbE8+j5X299fXS6CLE7/HGJ6pclvcZLLa8j+vO2kvL+9aHPUeJHxddyHqPEj9LFRlLy/vMYVBliR8JiZMc4idWBWAcIX6T8OLyh7W8j10Me2l5H610NLECP7GqY4n2yS0NfAv8iHteRKJ9Fj8FkQflfSxLttcCP+4EvYi0vI+9kmVZ4Ge5EiYW4IdexoO9PLX9dQjb8j5NzMYwpf3Le6VHy/s4gqVO95jVPsEVw6e8jwWDIbe8z6/4w3rQy1hYMAC/JVY8ZrVPBsMaA/itU+vZpK+uz0CxwK8Bibj3LPBj3+es5yzwc1nSfqblfd7Cp2FPgR8lcTils8CP62Z39XK2vM+yfsdS4rOk3eenUR9UlU5C4X0v15Mcfo/5l83tzvK+waFtrfIs76P+gDOos7yPyjM7PLW8zzoqhl/eR6T0ItKjHoJex9JzXrACqizvsyASq2x5n3ulY6nnx5Bp1lXe13odk/dzb8a3ZERneV/hxUvjsfK+HvUYwJ8t72Oz0CmV90FVCHrPlvfJLv1KaS+uT5Fo305CDj/aX7dpPFveZ62efdcW+f7LD4P2sb1iL0fz/WWgeBb2kSMDSM+jRz12UkIxpX0SUtxlaZ9M0fG3rh+D8Yd14+cMCpHCvt4ZfbHkwD57G5LAnIF9w6vMdurLbe6Ne0CEimfb+51sMVpZ+/tddDYwKGmDv5M4jgrPsx3+7I7DYe/ZFn9DruxU099zeav/pMkfDaVcnnT5s3UPv402f1QVkPqf9PkjK3dRt9Hfta4PO9vpb3hS4BzQ7WWZAZ3t9Te4R2Ww1WZ/V8m3R/hnu/0NfpuRQNv9jd5X+8cCrEzXgQUZbOxt+Effo+Fct+Pf5bGE42mrX4sSsG16/sGaKMk5afpH1x93BLr+cWig/bTt39W6jJcYkr5/JCrOG43/SG31iXT+o+mpqQqt/9jjDdbp/cf2wynQSfM/r6DbkJXuf8uONyft/yjooWbubP8/W2sP/HUbANpm8GWu2wHQsnzWaFsAYnNvXWnpArYkXGebAF6kufr4dgG82Of0VbQBpDj9RabrgJv12hyNAD0TQT90AmTtgJPOtgKkc9hL5kovQNuf8502A3zpBmer4eVTD5w9nm0HaMNR7ZR+gMow120IeFF6RPensx0B9aP6HVoCImMO056AvjGhnbYpoJTM9daugHbnopRu0BXQ3vHdtwddAblyZmvndgV8SGTwo6NdAW8OSThUHO0K6KMWxNeDroAgbGK+0a6A9kSm2GO0K+ClD3He8AfL+zqjXQFfHnGxwTfxAfsC42lXQONr9UNXQAt+6ItNV8A1yh7tCmgcT/HqoCugHeT8bV0HFsiz3nwTpHNNjDTaFfAlhnU89QceAGFzdAX0WgHrrV0BbZNKDjjaFZB+z17CHHYFXNtcuwLe3tr2tzVvJEcnPhh0BeQ2DnnJoCsg7xDod9oVcGALL3PQdcD+Y8f7dgWktboIefBCiG3UWNd0BSSvp9Rm8EaIfId5a1dAX/yg8/doV0BbTBPDDrsCgtKwU94JMS/xOzwWsDzdHH0pxI6s5ICjT4U4Bz5KwFsh6zZUo4+FDJsYKNN1YE2/v637gj4EH897Ievm7IMHQ6zSZr3BE92DsVOfDFkW+AzeDKE9v7ZdnugjCO5Z5YnDZzP4bfDE+pA3mcYH5M7GfPBEHw9QZtk1WL9TnmhnUXKMwdsh2qm/rfzAd2DUaXki1eeu0fJE8ywYyoAn4hON33hAxA7A7AvwRLiLPgSeSD+vFxneEei80eJ+lCde6yYtozzRd4qMR3lGxFdU1GnjA+vXfN6GxwOXDUEGPNGzHHRanmijfw4ABzyRQnzzkvLEi5gPfj3KE81PjfngibTPo9vm4D2RnjR7VjDKE/1t+sTyRHtDuHZ4UsQ7ksrwimTXqA9QwRPN5/zO//9diVGeOCjJN3eGJ1LHblxVnvjSERz7Wb8sok+UJ+LfHA95I8wb/1aeyBuE8qpRnvgHLxEMeKJ7FuMpT5Q5cO484IlcmaO4ZfjCyLKIZMATKdRyb4Qnskapzx/liX/ALQevjHhW0DV6lSfezkHXwVWeaPdxridd5Yn6UfKSqzzR81Ns7pIncibBq2TliX8Q71zwRM7a2OcueCLcn3PNC55ILkNOe8ET8W/EiRc80T2Yd9Z8XXjZoOEqT4Spujde8ET7NzMHa56IH73gidRsk2te8ETPt7Gf8sSX95JZb7wyQj7HRaqLV0boFO5rg+WJl93G+A48cX3F9ZInoh9leG102UbhgieS/+B7L14Z8fVMHhCEJ3rN0blufADjYt++eG54/fLDxSsj1hbxHV4ZMV/wQUj2BWybuYYnwq99z51XRnxHET+6fmWEePT6g1dGnDfixOVLOBc80foD/EF54uVZm+PhnKn1jNyuunh6mBd3XKPwRJ8tZDzlia43csALnshLyvoDeKIxua+Wct6Ir3I8XQdc4n35Tl+foh7pRab+ADZovANPZA5gG5ePEC/vmF7UJ1o/qgyvDi0LiK/yxId9jhuQFzyRuJfz7QueSBtazg4veKI3G/Cj5YnWmGnb8ESvqTgHxAfL+u6L54ipF+Ps8OI94nWvxAueaEzBvMETyTGMlXmSeP0u0GV9IjLsP+WJD3sJDOWiPpEX7NznqE+szR0v3yk/6Bzs5PUXPHHLOrhZouDEYgp3OWgiHpHBFCYCLam/uGCJ9Gjwh/EsdbaETRMtSqTMw1e5qUxklv1lXQBsCPgoXyaOiFsvGLHl93qBUsTRLFv1AxELqVyXvExMfsmKoSSxboNTvAuCWGqv06AgscZsDFF+yNVDjucv8GFDfSMvqhF7gscB0UUxYq9R6v/KDkfjyBeRVUjozkklYrN+AP9VcDi6oeljqUNsrbkhMdiw12+0F6sQa8i+L7+yfXN3ahB7+4hjoZsSxCI2Io1bYvgsfthdYNjDTh6k503i3rolyb15kjhwwG8QAa5cxQ0o7GE3C/+GE6ZO4mUgvD1as3coVXzhg4rnJeLeh3P0IMIe6eD1bioOm2m8/LCqvi4Mr3fDBxNZ1LZuiw27FyHB0WE9S/3XDRksRXoRobCoSTCqLxfsmVl95A0ULByGJ98wwZ5jsXfdIsEasCIQwfoVXBHPDrOpsrzgga3HQ/WlgSUGCiyQMIYlB+wwWCmUFRY7s5/eUMA6CV0RELBaJNe7ywCpteBY5KaksCklIesNAYyII2Gb/+nugPR34V8P1x1G4/uO3K2UQsJ2I4Fb3HC/sk/9NmWEXfLuWlC/nojhVsr8eM3bGS7yo7ye05a7xO9mz1akBl+ACcO94X2ExEisbpkA++7CPj7Coi/qQ8L9t6TvaaymZ7FwsFOsCPUhXWBMMWWDvRJJtcst5YuIo6dW7FysYm4ghx4YrlAvmHDFHah0jxOPl9mK5nmfxZCbWsHWVL2IYPQRoerihuy1GkLXAthrgxDIyV2ud/ds4GUsHP/+nGQ0T43gcq1A9Fo97xIG6HFJzIFg9N3o8RPFeV1gLK/CPM6sKG26uWrcozEXcWHeaCUDxSk3F40L6VmQZXmgCWMJqgI5DXEooLxFrklFYAQAjDf1gJw5VcLbxU0CGYeXi8tXFWlGV4t3e6AUsEN5EaniiSYwx2I8Lmm5OxbjsVhwXt4qriNGkaV4HGwasFEDWCjD+irE4yYCh4A3F4pJzf1K75G2bkXXLcRbxvZU/9Xo3VIt/qsrdvgN70uY1CQQr9fdMRYYHsvFCUP38RQReCj6axEwyPfhDnEzlReR1dVRnPcDwyM5r+ofCv6IDfwK98S6EdW5PKV4vWJYxT9leJxcUT7zAPFKb/FGTyHe6LrvWnmo84sEkwXBw02gEmr8ejjGbFHhV0eMZT0FeKxhh04biUVk8EDw4On1EQ8Ej/Qca1yW9pEGPyV4T+kPScfDReFCAPzdU4aHiyQ6eGB4Ld8gwH3K8LiRTVj4wPDS8MhvQPCaByqC7jt4DBiI11BdUwHjZa/zZ3Fe8/m9gAhvdYz0FOGNom6XvdeC8xU/wiXA3+O4hxvBQH6/wSl9eYlfgdw2vEVixewBng8Mr5ekdBIwvPP3CdpD4V4xDk7Fsr0enSNRrWdv8EfROaocAwuhYK/7u4EH+I4zTb8CxFmk8k/x3eAj+Adq9ZZrC3zXamx+Fvd+exxkbFN61wM3SMkDvCOhY4LpIUjdMlbild+WmiHBBb+fEjgIqvOKr17GTkS/sitbB65Ozx5v+s6F3qnL670kvSPsrunMzXqE3XUFa79ld/**bleep**KnJKyjiVOSB3dVLuCLpGNhKoZexgO9WBPqxX+DqnO6hXSBnW5WA3pGcMWHwO87ccKsU47VkVrVws5c6KUWox2nGzKqkGK/3DlR+Ad7TepwXEaK7EhaUTy1ej0/NTWF4qfhDKyA8TuqYYhBeoYThB5V49WEcBD9U4gEZGDwIr+DaFSbCq3vBH1OJx9VXJ5msrssF3VuJ10zI4bPbrwreHiBeH5fjd8HwUAuaXN7hZb5AeBQXKVLdc+bKfFGHV0dpCFaE11aFLBar8MZCgIyuaYrzSx3+73O6Cb+zCRMi1F+uUqEJv+v1gLrISQNA/J0ikNt+pO5+0v6vlbQvX0Htv9fipATv/n12OoV3td2u+Fl4x0G7KqECjxIpFF9+52EgPwx+x31BJLh5EeOlUHtSgFcR4sJZgHc3G6LMb0LwGuY4+JXm64Ym/C4hnj8KhlMXwdCpwEuFBRqx/o4+u4iQza/2xWmHv5oi6xF2RwNTJGjjVLU7XYuaDHfSCbxLcStqL7rjZ72MnbRuhW2nff0WmeCkAK85GsnA9BJukwGGQgFeEQDb9bSl3yrymHT06/k/YecU4K3q+eeK4LG8uHzbwg8dEfyOyg/mS4KXRczR6SzCY7Woexje8ibPpAiv72iwJgvxRruDuW9x6baHSC4wIB4Frn4Fq68ICwyK19t/7nVgvGbOrhfu2zb48itct+0FK7cuu/f9/IoCVGJ1JIqsIjxS7clF22Jirr1NqvCgOYqw0/e8FQlq7asUR7/a68mEJiivRYvOcVHeaPSpfXnB9ufo4VJz2a6PMu3p7dp2Q0CCIrwiAJwLII8iakUoxvq5r7itwPFqXByfTp/9KJlh9JbhdfTukXbp67Y6+GmgvB7qn9zMn1ys7fZ98uLSBObV7w/NEppXsxzue1ytrbf0Zv5cFeS9dCuZAD27Ovjb6LrRgFdXzvXadqy0y8uE6vmyKetg2aXvTWZ1x9IOAFOw17nm1Y8J2aMLAmRzesmWbhjKUI1NtwXHQ7fGZSfjCd1bd9+fXLSlA4D6WXbpG6Z+EL6uHTuoTxgfHXWMnXz1g1NAfxuB4PLlk0mlHt1BB3tVSZ/Vsy/6WSUCvhAxrdbjVcH+tu0D8Vsb6lcI1Et/nBchTnlqqmwnXyGu5fRLzPdXiOCAMdWIvkKLIv0Xr/0VWj4Shwv+Ci1fA2Iv/grBghhTF+tXiB2CPq61iu1DIR+vUmMWXyGK+f5gyrmQ6yuoL1+icp/eMi4javpojEcc+xVadnsnlP0KLdt8v30JD7F8/+Er1K2C0yYOtb5CZA3L98O3j637/sA0rPHjDTzHBCksY9H1f4WIH5ddD79Cq1MhW/V+hSjtpjfny5goB2CVN1P7ClEERJz28qWuCNpigeS/QlzexXKdcmr/2J4Ae18hXodh9tQTV3gNBl6EuORBrxaVyzXeXhx8FSKW5PWaF6HFJe6XZwG/QrwJgTfScq0HZBm96InXBJevfm4fmGJbOLwKsSLo0fgitHowyMbCX6H6CDpZk5d+hagGpu3xy5dW1z/sc/oVwkfgwrTcIsabV51oSfQVor3j8unYrxDvyS17h28fCgbrwmxK/BXqikCI07uvENe9eW7IZQRwXL/j+BXiKtjyFe6vEMdMdIZ8EVq2A39ZsAWPTwtt30yDKkIeiXsJqOjwR4fZFz3BH3nC7mVMf/AGsbMHg2QnBKh+hej5wSrXLQMi1+0Ev0LUFtEP2j3Xh0PwRi+zB4tilWuEPB3Cs6iAsq8QK4Je9E6EWJJUSL9HhaHm7trz+RCiZX0EVYb2A9Y0uCfcyimbrX6FqEBYdrr+Ci3jCJ4z+Aqxa/xBysqVYR6gAU9+hZa5Bh1kt8+y6vBtypd1h29xBIiSptIv8R61h1KCFyEiy+UTYF8hIktI08uYiCPeTONf/xs+SBut6dkAAA==" ), Compression.GZip ) ) ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Column1", "Column2", "Column3"}, {"CFG_FILE", "DATA_ARRAY1", "DATA_ARRAY2"}),
    EXPECTED = Table.TransformColumnTypes(#"Expanded Column1",{{"CFG_FILE", Int32.Type}, {"DATA_ARRAY1", Int32.Type}, {"DATA_ARRAY2", Int32.Type}}),
    #"Added Custom" = Table.AddColumn(EXPECTED, "CFG", each #binary({Number.BitwiseAnd(Number.BitwiseShiftRight([CFG_FILE],24),255),Number.BitwiseAnd(Number.BitwiseShiftRight([CFG_FILE],16),255),Number.BitwiseAnd(Number.BitwiseShiftRight([CFG_FILE],8),255),Number.BitwiseAnd([CFG_FILE],255)})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DATA1", each #binary({Number.BitwiseAnd(Number.BitwiseShiftRight([DATA_ARRAY1],24),255),Number.BitwiseAnd(Number.BitwiseShiftRight([DATA_ARRAY1],16),255),Number.BitwiseAnd(Number.BitwiseShiftRight([DATA_ARRAY1],8),255),Number.BitwiseAnd([DATA_ARRAY1],255)})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "DATA2", each #binary({Number.BitwiseAnd(Number.BitwiseShiftRight([DATA_ARRAY2],24),255),Number.BitwiseAnd(Number.BitwiseShiftRight([DATA_ARRAY2],16),255),Number.BitwiseAnd(Number.BitwiseShiftRight([DATA_ARRAY2],8),255),Number.BitwiseAnd([DATA_ARRAY2],255)})),
    #"START HERE" = #table({"CFG_FILE","DATA_ARRAY1","DATA_ARRAY2"},{{Binary.Combine(#"Added Custom2"[CFG]),Binary.Combine(#"Added Custom2"[DATA1]),Binary.Combine(#"Added Custom2"[DATA2])}}),
    #"Added Custom3" = Table.AddColumn(#"START HERE", "CFG", each List.Transform(Binary.Split([CFG_FILE],4),each BinaryFormat.Record([val = BinaryFormat.SignedInteger32])(_)[val])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "DATA1", each List.Transform(Binary.Split([DATA_ARRAY1],4),each BinaryFormat.Record([val = BinaryFormat.SignedInteger32])(_)[val])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "DATA2", each List.Transform(Binary.Split([DATA_ARRAY2],4),each BinaryFormat.Record([val = BinaryFormat.SignedInteger32])(_)[val])),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Combined", each List.Zip({[CFG],[DATA1],[DATA2]})),
    Combined = #"Added Custom6"{0}[Combined],
    #"Converted to Table1" = Table.FromList(Combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table1", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"CFG_FILE", "DATA_ARRAY1", "DATA_ARRAY2"}),
    RESULT = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"CFG_FILE", Int32.Type}, {"DATA_ARRAY1", Int32.Type}, {"DATA_ARRAY2", Int32.Type}})
in
    RESULT

Anonymous
Not applicable
2 years ago
lbendlinYou're a rockstar, thank you so much.

After checking with the database admin, it turns out that the data in the BLOBs is LE (little endian). or BE (big endian).So, following your direction, I changed the line ( for all, DATA_ARRAY1,DATA_ARRAY2, CFG_FILE):

List.Transform( Binary.Split([DATA_ARRAY1],4), each BinaryFormat.Record([val = BinaryFormat.SignedInteger32])(_)[val])

To be:

List.Transform( Binary.Split([DATA_ARRAY1], 4), each BinaryFormat.ByteOrder( BinaryFormat.Record([val = BinaryFormat.SignedInteger32]), ByteOrder.LittleEndian )(_)[val] )

And it's working perfectly now! I can see the correct numbers. But there remain three issues:

1) It's essential to identify to which serial number does the array belong so I need the serial number to be in the result (the seconds will be nice to have, it will reset to 0 for every new serial number):

2) I am not sure what's the best way to store the data becuase considering that I have around 3 millions unique serial numbers, each with 3 arrays with 1000 rows each, this could reach a billion data point if I follow the structure in the above image. ( I can ask this question in another post if that's better)

3) I also noticed that the code returns 3700 elements but I was expecting only 1087 elements (rows). So I asked him about this and he said that the 3700 is the the maximum elements of the array. There is DATA_POINTS column in the database that tells =exactly how many elements of data are stored in each array for DATA_ARRAY1 and DATA_ARRAY2 for every serial number. So for the sample serial number we have, the number of data points is 1087. How can I incorporate this in the code?
lbendlin
Super User
2 years ago
1. just add the serial number as a column before you transform the blobs - it will automatically be expanded with the other columns.

2. as long as you keep the table narrow Power BI has no issues with billions of rows. If you want to be cute you can consider incremental refresh.

3. That was not part of the sample data - But what you can do is cut the BLOBs into chunks of 4xDATA_POINTS first and then take the first list item for each serial number, and process that.

Anonymous

Not applicable

2 years ago

Thanks lbendlin, here is the revised code where I also added an index column (Seconds) that resets with every new serial number:

let
 Source =....,
 GSDATA = .......,
 Table1 = .........,
 #"Kept First Rows" = Table.FirstN(Table1, 10),
 #"Removed Other Columns" = Table.SelectColumns(
   #"Kept First Rows",
   {"SERIAL_NUM", "TEST_DATE", "DATA_ARRAY1", "DATA_ARRAY2", "CFG_FILE", "DATA_POINTS"}
 ),
 #"Added Custom3" = Table.AddColumn(
   #"Removed Other Columns",
   "ALL",
   each Table.AddIndexColumn(
     Table.Combine(
       List.Transform(
         List.Zip(
           {
             List.FirstN(
               List.Transform(
                 Binary.Split([CFG_FILE], 4),
                 each BinaryFormat.ByteOrder(
                   BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
                   ByteOrder.LittleEndian
                 )(_)[val]
               ),
               [DATA_POINTS]
             ),
             List.FirstN(
               List.Transform(
                 Binary.Split([DATA_ARRAY1], 4),
                 each BinaryFormat.ByteOrder(
                   BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
                   ByteOrder.LittleEndian
                 )(_)[val]
               ),
               [DATA_POINTS]
             ),
             List.FirstN(
               List.Transform(
                 Binary.Split([DATA_ARRAY2], 4),
                 each BinaryFormat.ByteOrder(
                   BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
                   ByteOrder.LittleEndian
                 )(_)[val]
               ),
               [DATA_POINTS]
             )
           }
         ),
         (x) => Table.FromRows({x}, {"CFG", "DATA1", "DATA2"})
       )
     ),
     "Secondes",
     0,
     1
   )
 ),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Added Custom3", "ALL", {"CFG", "DATA1", "DATA2", "Secondes"}, {"CFG", "DATA1", "DATA2", "Secondes"})
in
    #"Expanded ALL"

Result:

Forum Discussion

Accessing Binary Columns from Oracle Database

Related content

PowerBI no longer able to reference Access database

Database connectivity.

DataSourceError Access Database

PowerBI.mdb Database Access download

SharePoint Access