JSON Data parsing issue

[Maverick92]

Hello,

I am currently working with JSON data and facing an issue in parsing it in power bi. The JSON data I have is not consistent in length. It has a variable number of columns in each row, as well as there, are sublists(Old & New) in some rows. So when I parse the data some of the sublists of the data is stored as `Record` while other who is not having any sublists are parsed properly. As shown in the screenshot the one where the old and new value is present those are not parsed while other rows are converted into columns properly.

I would like to parse the remaining records so that I could combine them using Dax as per my requirement in one column. Right now the columns are a mix of Records and individual values because of it I am not able to expand into new columns. It would be great if you could let me know a way I could do it in M Query or any other power bi feature.

Any help would be appreciated!

[lbendlin]

That's not how you normally parse JSON, but let's ignore that for a second.  You can use Type.Is  to distinguish between values and records etc.

Type.Is - PowerQuery M | Microsoft Docs

[Maverick92]

Hello @lbendlin ,

Thank you for your response! I tried using `type.is`  function in M query but I am not sure how to use it as I am still learning M query. It would be great if you could show me using the sample data in this link:

sample data 

This kind of sample data is flowing right now through AWS data source and I have no other option than to parse it in power bi.

As you mentioned before that my approach to parsing data is not proper, It would be really helpful if you could tell me what is the right approach to do that. Right now I just want each of the columns separate and if there are any subcolumns those should have a separate column with the main column prefix to it.

Looking forward to your response!

Regards,

Jayant Mandhare

[lbendlin · Accepted solution]

Please indicate how you want to expand these records.  Do you want them split into columns, split into rows, concatenated inside the cell?

Here's an example of how to split into new columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7V3pb9tIlv9XiN4vu0BaqJvk7ifHSafTY3eMODuN3cnAYEuMzYksenQk4170/76kjlhiPZJ1kZScCgaYtqSqenX86h31jr/97Yf/+/jDOJ8t58l4eZNNPv7wnx9/wIgy9PGHFx9/WC3S+bdPCQvF+tPxNEtnT78mfP1ptri5S+aTm/tslt2v7tff7b5YLLOHdDa5eUge78um8/Sfq2yebjtY/yq9f5jmj2m663b94ZeiUT6/ma3uf0/nTx/f5av59PFmnizTTQ9bcnfDlN8vnn6++3iST6fJ/Ca5z1ez5dPXW4KrrXYf17VK/lVpReODL1ra/XOVzJdpOY3quNIPWnpKZrNVMq3rZvttTR/ju3T8udiWbHKzzNcfv0xn/0iKuQd/Sb7k+SJ7EVy+kn96kyyXxUZm+aymr5tkMpmni8UNbvsBqfvBOFs+1n23WO72Hvjyj+zhZpxP0oMjmIzHq/vVtGi2OV+bE7P36c3koMv9bypLu/9VdUHXIy2zL+nuaP/5w99f1KEsojLKGEUMRFnYBcow4pjGYXT45Twd58UYs23/6PDbNJnPntb3bHs8aqC6/Xi32YuCpHQ23lvnKpaPAMjPAcYesp1AluFIhmzBLjEI2bgZsiDLqwNhEwZrAIjKfwJFEW6FIvZY7A6Lb2eLh+IeDl7/+iH4uEKIiOBqNb3PZ/n09vFFcHXmwdoRWGMArAzDUmwLWC2kWNeQZoQg4iHt2evzQywB9E6MCD1xxIZIYM+EB0Xsu/cffn53dfb61dvzHRO+vio+vA4uy8/e/vo6OC8Y9Ov3wcXFlYd3f/AmoZn07OHt4e3hfVzwFpByvLWLHqdyvLO2eeB6Sfo7wOL2ocRj0WPRY7EvLEYCNBpHIYRFiroQe4vheEyZaERu8zvP1VUNelsxC0IWU+5R61F7zKiFrMcnhtrzd786Ra2HrIfsEUM2BOxLHrIesh6yxwtZ7ASyyGPWY9ZjtifMEs9mPWQ9ZE8GshgRiM1GGIYs7QKyjVBtQmoNTNdGY45DRlsB643GHSL27avzF8HFxblHbifIJRQyQyEMPqpSdjLIjUTIQ49cz2t7QyxBBP2I0Y8ID4pdEsFhdB67Hrseu0ePXVhi9tj12PXYPRrsMsBAxTkFnZ9od+Hrzl2kOOXeRcpj93ljFwiKJzyOThy7GKnwXQJil6JjyG/xHOD7If1XsgjezZd3+UM6ycaL4Pohny8Xu5iB9+ld8ns2zQpMFYAOzhaLfJwV27Aow3SDwKN/CPR/55wbc4/+b92Q3Wq03gCUEL69Nz0b7x/IsRx/EMcYjPQ7ofcmhhkx1Z4ZPQYch+hIkGzFx3/LxncFkw6uV/PbbJxMCzaeFox6mi2WC59RozNUUwHmhRMEgrXoAtW4AGFMYtaIbu/4cdAqonS0vWOPHdcet93gNgSM2XWZ5rphxz7T3PEK1VaQfVMMm8yWaeqx2wl2GZSLlRI4qv6URGka+6QZw0L3/P3bD2/Pzy6C87P3r4NCMLr+74sPZ79+uA7e/RT89vb857cfvCTdDao5kg1dXj/2uPba8QljmoYSppmIIOWYk2ZI9wFWjCIq2o3SoefAXt89MSQKwWWZGeEYcrxkLXYqWZFlURT60KNnh8Wzn6+CH4PrZVpsQHCdzW7vfO2DDhEK5afyCPUI9Qg9FoTK0myBUAohNDRJIKdY3Id45ydH8LlMlnfp1+BqnhWX1Yvg1ejdqNAGnYTuccyCcHlXYHOepkvQT0oZUO/z4ovFsjwTDci6fNuGLRZRFB41wOS0cCEBMzR6fJ0Cvt4ln6fJbBK8zGfpzqHwlzybwXjQAxhjiEbBb3k++VqcgBfB9SpbpgGGHJw0oPZymuf3n7J0Ogl+zqbThQPEkWNGHJwfikG+xc8fcrwFc6WrxUngzguAHaFFzhkheAS9O3YJFu/+6kx9mnxJCslvEvyU58ug5FRns8/TQhK8ciABUhIjHvw2CjAJLrNpGrzPkyc+he341E/JvNix22U+c8WnniI2jxJ5YIIl3rdo6KHnoffdQY8B0MOgt83zFxGfA/Q2pRaT+WNwNp2m89vH4HyeLctn8RJ9wXky/6asXU/T9GEv6MsemiEPXibz8TQpRs2K/093sCSIW8Hym5HEmeJ21KYSEJV0+8rnUfmcUNmISDcM06OyU1T6FwKPSo/KY0KlYJFnlacKSntkWaGnES5tYDk1nHD6fb4FPAeceOb1TJmXQF6i9KD0oBwOlLI3dcwRnJGrpdwDUMiBhiiuhCh5X82TB/53JrWuM+yguMcMOyIEMuxwAWfK0kYlojimyKPyVFGJGfLJr4aDJhAIGIImmGMIBESYiBi14pI7BGZE93LSfI/49NDsV4KVXa1FTKBo+5ZYeyt3mqPQKnGr3yf3fp/fN1jAwtcczk1hIFlGiPtC9KfMvCLkhcvhhMsIDGI4Fnh6xc9j8zvGJljN2mPTY9Njc3BswkEVHpsemx6bw2IzIrLBlOzgdAhN0mKgOY5kiIaq6HEYiNoqBPjA4OPEbX9ola2pMQfdvlsZqXEVABrRra+QZ7cnyG5LI7Nnt4OxW9nC6wHsAewBfCoAprINOOYMLMPTHYCx8B5Cpwtg5j2EhgSwbCiOEIeKy0dhF/h1rfBuastjLHxJj0FV3p/yabK4SyZp8Jdknqb3PkFqhxiWY1VChCAM484grOh65NHlBF0fsnSRzm73Kmvsh6xcj85HrVg7L47ANF0E1+O7VbFlywKgo1dgswME4pij4Oey6X1wVmz6KrXC5Ovp/dc8nwRXyfxzIz7fXrQhVKDwqKNXIqikNGIYxOlpWJc3zDYU21x8vqR0DzcCFV5cPqgmjfsUlwEMxzuwegh7CNsw9e8Mvf1hVo5PCwVYPiBqCeU2NVExxiMbG7PrirN4W3DXFWaJGWjJNnR3eNwSpoZcwj3vHY73yq+9GKEYkp87Yb33/yh4OuM1SCw/2fH8Kt74UfhTHAnWrHjkq3SWLdLg7OFhmpapYF0YlYo9RcH12W8fXl9cvA5eXvwV7FMZiRf5Ijib3aZTMG/nEyjPofKXB7CMERLCAJu9IZIDxmMSQfkYupOFveHJy6jP6UGGQ1aiHrmcB5UHVR+iIxoYVJ5ReUx5TNlgCnh1/JbP7ABT8elYQilD2xQN3nNgIOReryZ3aToP3qzm8+TeiYYXkTC4zlfLu+DNPE1nwfviiM+D9xMriL/+kswWX7LpFHoB3Xu4/LUN7iwMt7kwjlXNk9PuCY6hSiR4a9E7cqhzwQXlTBwac0Cos+J3lFOXOYc84j2v/hHxXnk18ARCQDdd0Z2LkFMA0xLCKgCmrEQ78/j1+D1h/AKBbqGAI8ZPR9gOQy5M3Q7I1tdrWPSythDViESnEaL6Pp8l00nwS75IH+6CX/Pyv6HC588Z2L3BWUCZkxiYBhBj0QWey9BpzkOLdNfj8giA2G5FtOfF3eH4MhvfJek0OJvP7vLp8g8HJd89hkEMQxmWGIFk6sFtysfho9eaz+EkAObx1BGeoKxIbOuYdbqutTEKY2Eo4x4BZp8DYl+tisWaBRfJ7LY+yMVD2AGEwQAXD2EPYQ/hU4Gw/KgrOPTSE5+EnRghjAinCLdHgyNYcnbsuctbMFxNef+8YOxR2xFq5ecdgsEyL909z6qqouQY7LZbo9qJw+n8ylt3ukKUnAeUstA74Xre5JGkh6QQfuuAmBNmnQiVhJPaqC2YRz3VBPO42vDsEVcDFw7rsxOczQowBGfzRTpLVtPld5gRaICgyRA2q0Ku720lAE1fGjGhYcSCRt2u0rbbSkse2XocExOfcqR3tgnbUkFTTCdZC7QYZnQM74snhSkPp37hJNs145hB+hwZHE0eSk6gdJbM81nwc/7p030y+w6Fzf6wJVsfC1ZFIW+2uKMc0IxztA3nNEuwc3btVMIM0WibG2FYHNP4OSD51XwUvPuaZIvgIp/cZR7HHeFYtnmWOIY82jrD8fFA+DhqID0X+F4m89t8mi2DX5LZ53Saf/Ug7gjEcqRIRLY22Iq5pxsMRzGNaoD40+vXHmptUCMVD4D6XHQNhQKv0+mn4DKdrFNDv5nnqwePt47wBlQwCgmY0xnxLgDXyDGbGGYNSDeRWYwg0so3vctbh1zzepwvl8G7+dyN6oojhIM3yWQxSQulOLu9+5o8WiH6ZTYv9uX2LrlvxPbZRRu6KSf0qNMfALW0IxpC+WO9L8DzwJ5nlh0hSU4kUsYAnzizJEgw05xBeGuhOWrMYiGik8Dty3mWzIKfpsksuXVl8y2Uk+Aqm+bLb/L0q/n6uHveqYJ42TYcIQanDurE+QcjJkKObGzD3vvgKLGezOePwfvk8ffV3D/vdAlh2aIkxLYOSiV3UCfe5QiVARw6UjDeehcOCzLyLMKdy3wC6XSaBtfFns4KldTDrCOYyYak8vUFDJ6kHbFKRAnDvpTuc2OVHrLdQDYGil8zBrnWdoTYACMqiIi5h6yHrIesCmTBrD0UjEbpjMsiiiLiIesh6yGrAlkwggWs39cZZAmmNPaCsYesh6wSZMHgFQp6RXQG2RBF3EPWQ9ZDVgmyQIBMr3JxUArGjBMPWQ9ZD1klyMreFCWX7dH+hBFljG/D6DxkPWQ9ZFsgC4bKgY+poY9D9VjyWGrAEhyuBvoWdWfKxSTy7M9D1kNWCbKyK1HJ/uA6Rl1JrCHZVSn1kPWQ9ZBtgSzolsSggNLuuCzBjHnIesh6yCpANkZgxk2w2meXPg4R8j4OHrIeskqQBX0cwPxkHcXJNEK1Cak1MN1VTiGmkXHiGHz4nwNm/5oVCz1bBi+T22kx/xfB1Zk9jJkQLDjPl4uvZaXQi2R2PAFxjBw10kHXCAbHwJ4U0rFpjSSPdEdI/99kXByUx+ByfJ6DyR30ME6jkASXye1tln5cFUqY+Dca/9ciuJomYzu0v53ns0kyhTrRxDpGR4P1w1NdfFBcjJurko62Evks/Vp8wtGfDUdtv2EowsOmLNw0Bqa1167E44/r/+01na2m0z/7J7bTEeNTWh6QWIUR2UgmVG3MiMUR3LjDBRKHIxafxAVI1Ajmhd5Ybc7XLzoGI7O4l1GLFY73muJivwiP+J8NnGt/i54qTeyGJusSYGpD80OqcenE1fH+RuFoG13b25GKDTEnBI6qG1vmAlBaXS6fiXW6tT8BmWOvGYHgqj8aUbkaqo263wpwRJVmVYT1QCoeYbZ/akjxt0o7ckipUD1tPAwrp42HqGN25JpYpRG3+QC2BxXjEWf7owKgwGFxyzEzJhZjIUbbalzbi51HZXcqxHKz41o95qLzfZS4JikvWZWWFamCq3I9zIWQrjgRlvFxKsPGo6hyJ7c3Eka7IVOJd/dwdxsi+r+w4CEV2uHRtsbP9rAWf1OVduRQfOhhhrS6k6EaL67eAN2TGva+/SEekX1BMCKjWGUbQ9KsHwDXcZVr9DA56CLudsTQCEwRG2EKEFq1Te01OZQRyaGyoyohqt3ZBOH48JSoTsuMEcZktH0f3Co3IW+bHD5EavFJpKz+EUlMLIV/pKgODU5sSaoinWJfgipuxbhbMFSHLE0RpGVxDtfFnOGui97hHxFdF71TIbYiJpBSoTZq1zJDKgldhfLetWABUKnQKI5r7ox6RfRwYu3XEu6dKcRx7bwGGVRFGalp/W1c+almn+6DPdnsSC2hmNaIIFWz/16b8ys3a7kjbt/YXTMP3Mnc6ywbDee3xtLZsFpvZ5/SYnb5ahG8yhZpskiD64d0nCXTbLFcBPmn4DpfLe+KL5bpfBb8li3G+WyRzV4E16PzETBWx8vV/Nq3v3xA4+qz3b50zUQU6x3NEFYdal61aoUlZsNW9pmJxlrilskBWrjGRUsc8Y9uJoQp15lMdNSTEVoMEAm786J8AFC7AmCkW1ZFqm91hPuYFYtaZxVSEx190Flx2j4rc25/eAtvj4XCk9C+RrGxfao+MUqWUolaE/6B2rhBnalMb65PU3Vx33Qy0TqpRHOiJI7pdzFR6fCCTwPm1hDJTt/L6hCGRjRqWB1gliI+fOXVWNBiFauwJnjzbPw01RaHoXqu6MySbkeBi26UZiLzGxRV+M0AJMQ9k9DtfqI95XHfJ6tWJdJeskJEEJslqzRtpY87PW7W/RAWWezYbi1CR8eHcUfT4jbnsE4OHIQYN6CIhichxlzNiNvd+SKGF2RBAeuXgu72gfR0PYZDXo/EzTVSiDkhP5bLsZD63Exq+OuIcCYcTSYcfjLRxqHPBCTE0dGIbJi4o1WInGwotZIgHa+rHTFO1pWGEXWysAxv3XoHnAxjoZN9YSxev5Nb96N6FXanMSmT0CSpHhEx7sBnR4yb88odqVlMKF7Q3YkPjjQr7uR+5q7mtE4DYt+PI9GK29ywrvc7cjOnkDrqx8nd4kQlxwhHVIwi625YXL5DD3dDEU4IHxHrA0eI9ZqWpnA3LupGw5eHdMDRiaOoP6PRQy5CPhI9E4Al78O1TbJnWUqmwuzV98mea0hElQxeKrhmnW1ulcOV5aa94RKXlX0iahvl9rgA3qr8iMgw6yuuhoaU1dCi/icl7TExnhSV+nLmEqlBhRyvN8hpkcngQ1xyDsnAyJyMSCJjbZN01JfpJhGZB5Ruc5UoW53+omqwCEYbQc+kt3KJJC9vc9l8PbHKJhTnw5Q6ClAniIVXpPEuSoSUV5hRT9IFJqhhT4K5OqMAdEqDac9GYyoAqAzzNgiQstbCja5Wc62QhkQOs7d4hWbQZbQepPcVZgRVAyyHMqoxIl2pg5FCaZUURgTpW4lnMkunagKOU8liY+AcXtAqVR7pgAhz4yswrZ2aZmiFNdcOoSXeBPFEba/w+26B0pZAroyoPTMKeAtSEo62RQTsNhKYrLFCbs7nRFhjFzDoK6w7SWYHyUL+likxtLjYUUGOQD8M5etzg+7aztrDMxqWo70xOvTjN3BXsbeNt7godTuJzmxK7WQzG7qxKIR8m2MTWe28A0fF9puyPTbX7vjaaRC7SbR4QXc7CUzJQMeXWN4ddm4dzk6QsJyFjWxmTbw2hgHzVWl4EP24VsKe561xgWQEOTMqOEE0WtJaV5cSO95Y3s/dqImtpBv79bW84bcObIsm0Zlq3Up6NPTAg3iSKvondD2JeMB93yYuaUh3cJ0u81nwU3KfTR/L5Aav8vEyL3u11xVtARcTy4tbmclo3b1YYMJGWFhMzUHinBZvLVUKGk7Gwe8vk7t5ms6Cy+Qf2eRFcPlqmBNtK5hRq0vYkWxjNwkbpc6RWE+YBf1M6Ymlg6PT1QOa8sCmZ7Z8ldJD66t0nn4Ozu/S2e2AWOV283bj1dpih2+dxE48t/A+HfzCoXYbMZzgSm31cNTde1Yr7TYCArU8dnaOvVbzriaF0aWcDEe4DVsrKRdDkW59WCyOqmIAcQeTJjZkCyt1nq7dV0yUIsurPLQz3GDUWVBi15SzwUxOsRhkYBxt4xhM5cbh5acyKQ7CNpOwC4xWdBRongQ7rCyiOYE9jxqXXgVMpcqHopOL7hnF1u1lM7026yWQN4mTvCn65sIjndCx7Mta47ejQ1+DAZezV02m82nY0LJ9WdWMp5H60N7YOj8VmyfKHncUwwnbNHxboUfKyOKdtZpAXWUWTIK5vscE5lJlEor7e6+v5iU3whbkZ68PrWri1rIT/dWUjkXY5GqvQof+4dQNAIGpAJiIwZrCtZY05iKgm3OHNB1aqP3CEqrp+wjRUS0yqd+BjHsutEGDJdyXt5r+Fjdn6VToQC6OZSSsyPeI9lREVL0Jke5x5XKETNgYLggmTZX9+fXfHsDQLU6aMwlB1FTqUEaaK0JCWknev44T1SMBuou0F0TemiflWosaKD++TgcxqQpgkeZUyvgmI6ZdERdw3Oqu7CzFuRN5QWIomGgdpWKprZmAHF2lPQuMR+HhQfxWnEZDBoUizghjkeaBLvNcVK89TUpAnddUGm7PDtr/JHQokdizvuMRBUhpS0sLkiL3Emvbb0EJjLWE2apRw7C24AJsE9dlKBIlBjK2rDeZ1kQwsM1WD7uJ0CSrKj3bqYgDyY8DegrTlnGIaKna104II/Ch0qCiWhLKZD1kltCjnw6tMtetYGMgkjSmIISGlrV3QrVXj8mKM9M1jlG55jvFSoWDnNwMNKrEJfMyUFkTVJDqELYEbkOPG4De7uKWMuCErKZEqQYhonrp6sJDDoY3CAuzjL51gnOOK3VzeKESEc1DFoZRdUEKrSZSyvkGIKWMu9Q7EtzeHhRROGBThwpJWmSWnmpiV9WsO39rPoKiN9qt/LvkWzoL1Fz1qL19CCl8Gu0jCWyaDmFSB0PZtyAriraBG7p+aJ+XT4G5igVACKY7CQY8gkXI0BVIX1mNqvLmWnDT3gsSAzbc+IkVNIWLrO7vk+D1fTq/TWfjx+BsscjHWTHbxYvg7Ww8OujybJxM0vtsHHxcIUTEeX5/v5ply8e99vulNTfttdajwk9wqGkQw5RKmZtECAluOi+S0CR0XzTNO8Dre9y8vXsHTJ2hBxvbfSL2fuLNbceO3avuyoMrFmnoYOgBTxqxRThx7wmpObRV+8Fot4vFcDS4eQ8RolYCvrIrJSCq1Hkta/iiDrTwtC7aDjYGKWllymMzWGlR9h1Flu0LSY9ZdUAtSi3ZrR2rKTql63dr3AHvwjaiPDhz7nM+YLSX8tBWgquIEHNVaF137IiFQw0d2xxzHIbCtG2h+cLlZhWbc+c5DRUHDgkuC00MMXJxnw4wrvNCDKqR3hEbZJ05nAFbrXE40GqFrmt1qA0boY0VrI9hwaTgfXIbmQDtk1Jf3MJQLFettg1I5XVhTQOQPyANWH8LgczHOLYrK6q7BpCfNNEW9+Vuyk40uwDeFw2EZ7kf25NlSETVjZb1x3MxZXUPxv0ML9eV6ZObYSq9K2BMtYVEIBgCg8EQen3YXTJ9CqvdEK9FgSjEc+mpKYy12Y0o/lUe62lxHPpaSCEqr6jF4HoKD5Y9prUXM4b4DeqX38g0rCnoayfkl+g+LyZ5dCs8kT5Jr2HPmvtfdS6umb9S5i+9ca1NqkR+dLfbPbOSJg0JDZuIr7/Kezk9/EjH1+wklGRlgcL+9EZScjEh9g9R+Ql3W9+xMRhqbbNyhwFVG5gjFMhxR0ZviSTG0knA7uv4aAVYqN0mXY0e6QojchdWB0lbGKoZX6sLxdjRpi4IxRW9ORShXhdQFBV1nwKsmQRJvNXdDqlynaXW3kUFqSHo1yGC4Sp72Hyid6DqMvMMtRdtsQQnNBXtYwVFD+zlI7WwlQ6yH463o09bSF3xKu0+qmwbC32IcmVVBAolqTogG8SS1IyvNwf4ytTqo/t6xPqja3UhGSp5IaJqdVETza2vUx9fdt7GWUsB01ZXybq/3oin1gdHyFZRbsAWQtVMO42kSH0IXTO5OHHxb+3z4/I4lv31pRWHDnSh8nWrYtaN4xCoO93QR1RN6LhmNP1tojR+MbpeB4CNNOJwOouGbuJIFhNwtKnqrtXPupBoRTFku1RLB8E1N8lkMk8XixvcdMNTElwn8yQ4LzjJEuplnC0fGzp4Pf0cvJnnX9Lgr1lB9W0K9bFYNh/ztxdQqz+yh5txPmlqWDKoUHcrpCU0S5LQkCCvg9HdwKF+dPViLdNxfh+8SpP7bfkHB+NLXaikmdRoGY4wa7jGnTyXtiRcXtPgwGPhxTaGdpl9qZ6Q/eHUFgZ3fBLhMctsLyZ7cfD0H+OREJoBeUxRRlPICVlOAwMKnkJT4tYnTmFErfPe1ljPOyDCTGG6wNVaZ4lwPN0oFk5l1ELjCG1LiymtTwTWhGy/RjANwVhxt+tKQuFW9qfW5kYc1wvSqpNXbCgiw1WLsRgx01EPXvCwqFyPjZ4sQOA57Fllkhu2xcXQjaHDshqwhdfFXtE8ZWDJ2dgOiW/cLDllwqaIhNrISole1TK8rjMIGbXVOCKlcmNKMuAYpb7Osk8T6P2qNC51rPAqDQpHPCk0hbJXws3klKOqh1iIUUgOxgQ1YTi5r/mFI4nUPWyMlFEuVj6EUI6VbQ6GnqnW2lxzKdt81ILBF+exSbPQdOTCkYIHjqXgGlazslVTTzb6DUlcmzl+OlMimWocZyA1DI+cu9gokK1znMNq8jxT/0bLmpVdxNjDubWNMVgbpuHMsuaY4NCxKwF9yp9xlOrfOvGisrB4NAtL1a42y9tYKSmbYSr4FtSo54CHMsU6XOkQUzaKO+d85pYFWvqWKrD51ttYDAfTNtqMKscTEY7gDLVO15/HbMSb7BvP67ipTrfTsubKq9RooKgrzK5QTOFJc+5SSANGVVVh5aYaF7D8VC5UldHquBipnitpTAL6VyiMSehhLmWtmVJOgAd9hVGp6kwJ4J7PvkXmNzw7/pLcp4vgTfqYztcvjR9nGKHgfTZOg8tslgbv82QSXOT5Q/HN9SpbpgEtDvjH2YfVYpxM83yRvAjOLoKAcobEwfCVnmu6/dZnTYdGyxbBfoEaBU3MtjlUtF05HdX0rrTTk+qS0II1T1yoK0hLvyrGPMgdXIY2q19VpYbFK5dkXKNYQoNTyRqF9et0lQCAUtsbq6d1ySaUlo9wveUzZE7SuIyoi/gRFtXm2zRcSmNLBQmr4kSj0UVOeV/GTPXAz6mxgbhkS46CSi0vk7qijQpFdnT0ZVotnKm+ysYN5WcKDZlJcu78llavmasftLi6fBP8GFwlq2nwMp1/zr9myz+2XkVdn0158hrXCDB5XpNgRX1sk6Y6kHKdQ8W4llaDRys05RqHNbVrF3ruNLhFYotLpKSia6Mtlbl69xiSBh1qTIOGGqco5OGIOMNNjAoVoHOXqOObbQNLuEqW82z8OXi3TuUfvpxn6WzDBfaW4Oz19c3rX8/f/8/Vh3/f/O6pWfmv0nT954tNcYBwglM0/pTglEQpSz6R3wlmqBBXP0UCs0n4++ZX/2F4l/WyLVGMR1HoalswChEZde+ZV60epnMOQU1UbdBa65XCuEB4aV+eAubeqe2qt6by3PVMzQ2yAlVfW/rwWqZy9WONIxXJkV2cK2t/1bEpG6lZkKoNOVE2FJbJuavhX7uabX2us87BsICPhdtJXLDL2F7CpNpGGKPrvPGBQOdiN18x1uq/pbhkjGpXja0YYHrRXyyWSjazDbRUQrUGq+WpitxkEiv66YGHSfWhRpvkgyobK5UZ3DhHKa6T7Imrzox41TEfM8V3J3lUdWc1wPGrg0ozKkST/gfFqo74g3j0mRsta4tNa8HVPChO9LA4siqgbnlXTU+nNq66eTIiVRGZYtS56AYU1lW/HqQ34jLdr9GwGIrvBts5Yq0I6bLWEEgkTZRvYcxG1FindiN7rWkw92Yh5tUIDB4MGTVeL84waX7CUjwkXI3r2AmaTKrxLdTvDIGrCl9M1gqf0sjmWltNBWsDHLqqUGxxKnU2a/AlY5q1sfcNCWKTV+cofSdHUOrrdvdJFNNNy2F2T2VUyYhUdZRpNOv3USG5nei1G4UizVBJF0Yp03tjfjdf3uUP6SQbHxQEzj8F75LP02Q2KVOfzMocJ1KvShlUGEM0Cn7L88nXZD4Jzr6koxfBxncM9nJry6jycprn95+ydDoJfs6m04VJQpXLt4YJVVhEketqY+2nQiccx5WXmdCW2w4vDNUqr8DNphHc8n0vFOtnoXA4KtWrtoVSjSWnxQ0fDcUf2yUdTLk6pzPnVQdyc/GnpriBVXNzQoPL3jk6jLKqm/HOzQyQO5HqBrkBeR9BmFxyY1QbtXr20Y8YqV8MksOrugGnNnmd8bgGDZkpsWCmu/bxorLR3/8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"contract_id", "user_id", "client_id", "is_hard_minimum", "is_stipend_payment_required", "employee_id", "vendor_number", "hourly_rate", "stipend_hours", "stipend_dollar_amount", "minimum_hours", "minimum_dollar_amount", "maximum_hours", "maximum_dollar_amount", "maximum_quarterly_hours", "maximum_quarterly_dollar_amount", "maximum_annual_hours", "maximum_annual_dollar_amount", "check_paid_to", "check_paid_to_attention", "check_paid_to_address_1", "check_paid_to_address_2", "check_paid_to_city", "check_paid_to_state", "check_paid_to_zip_code", "is_accumulated", "accumulated_date", "accumulated_hours", "accumulated_amount", "is_active", "employee_record_num", "employee_earn_code", "vendor_address_sequence"}, {"contract_id", "user_id", "client_id", "is_hard_minimum", "is_stipend_payment_required", "employee_id", "vendor_number", "hourly_rate", "stipend_hours", "stipend_dollar_amount", "minimum_hours", "minimum_dollar_amount", "maximum_hours", "maximum_dollar_amount", "maximum_quarterly_hours", "maximum_quarterly_dollar_amount", "maximum_annual_hours", "maximum_annual_dollar_amount", "check_paid_to", "check_paid_to_attention", "check_paid_to_address_1", "check_paid_to_address_2", "check_paid_to_city", "check_paid_to_state", "check_paid_to_zip_code", "is_accumulated", "accumulated_date", "accumulated_hours", "accumulated_amount", "is_active", "employee_record_num", "employee_earn_code", "vendor_address_sequence"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"contract_id", "user_id", "client_id", "hourly_rate"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each try Table.FromRecords({[hourly_rate]}) otherwise #table({"Standard"},{{[hourly_rate]}})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Standard", "old", "new"}, {"Standard", "old", "new"})
in
    #"Expanded Custom"

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".