cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maverick92
Frequent Visitor

JSON Data parsing issue

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.

pb1.PNGpb2.PNG

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!

1 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?

 

lbendlin_0-1634777379702.png

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

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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

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

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

 

lbendlin_0-1634777379702.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.