Power Query - Json with varying nested structure

[Krishanu]

Hi ,

I have a json with varying and nested structure (below) , which I have to load in indiviual columns in Power Query until the all possible levels. I am struggling with this , please help.

 sample json  ->
[
{
"id": 232803570,
"refreshType": "Scheduled",
"startTime": "2021-06-17T03:03:05.367Z",
"endTime": "2021-06-17T03:07:39.127Z",
"status": "Completed",
"requestId": "81dc834e-1cc0-42a5-810f-c6e8d5184555"
},
{
"id": 232237246,
"refreshType": "Scheduled",
"startTime": "2021-06-16T03:02:03.913Z",
"endTime": "2021-06-16T03:05:54.223Z",
"status": "Completed",
"requestId": "a9f7793c-2775-4c0a-a9c1-b293de57d636"
},
{
"id": 231673035,
"refreshType": "Scheduled",
"startTime": "2021-06-15T03:02:02.893Z",
"endTime": "2021-06-15T03:06:33.77Z",
"status": "Completed",
"requestId": "42d39274-42fb-4e9d-8c58-ebfafbc81a51"
},
{
"id": 231110445,
"refreshType": "Scheduled",
"startTime": "2021-06-14T03:02:07.81Z",
"endTime": "2021-06-14T03:17:59.073Z",
"status": "Completed",
"requestId": "f79928b9-dc7c-47f7-9f3e-00c17ae97029"
},
{
"id": 230554481,
"refreshType": "Scheduled",
"startTime": "2021-06-13T03:01:05.18Z",
"endTime": "2021-06-13T03:07:14.793Z",
"status": "Completed",
"requestId": "66e9e5c2-7923-4a54-88fe-fc6d2da27dc3"
},
{
"id": 229998006,
"refreshType": "Scheduled",
"startTime": "2021-06-12T03:01:02.697Z",
"endTime": "2021-06-12T03:16:01.957Z",
"status": "Completed",
"requestId": "f3b3091d-df84-4d6c-999d-aff0f817eac3"
},
{
"id": 229431144,
"refreshType": "Scheduled",
"startTime": "2021-06-11T03:02:02.683Z",
"endTime": "2021-06-11T03:05:13.053Z",
"status": "Completed",
"requestId": "de9fd33c-28c7-4b7e-812f-9221749cacab"
},
{
"id": 228866692,
"refreshType": "Scheduled",
"startTime": "2021-06-10T03:02:08.497Z",
"endTime": "2021-06-10T03:05:52.66Z",
"status": "Completed",
"requestId": "027542b4-bbca-48d8-b604-f475f557ad4a"
},
{
"id": 228302397,
"refreshType": "Scheduled",
"startTime": "2021-06-09T03:02:03.26Z",
"endTime": "2021-06-09T03:05:22.737Z",
"status": "Completed",
"requestId": "36bfb3d8-0136-4644-8562-beccbe8c387a"
},
{
"id": 227739640,
"refreshType": "Scheduled",
"startTime": "2021-06-08T03:02:02.41Z",
"endTime": "2021-06-08T03:03:38.683Z",
"status": "Completed",
"requestId": "aa18bacc-3647-4290-9831-50a2f5554f42"
},
{
"id": 227257806,
"refreshType": "OnDemand",
"startTime": "2021-06-07T06:26:43.4Z",
"endTime": "2021-06-07T06:28:27.347Z",
"status": "Completed",
"requestId": "f9b1ce14-9c57-43b0-8d5e-ed637374a509"
},
{
"id": 227177102,
"refreshType": "Scheduled",
"startTime": "2021-06-07T03:02:02.783Z",
"endTime": "2021-06-07T03:05:05.517Z",
"status": "Completed",
"requestId": "b45ec1d9-3a11-4cb5-b064-64b554c2c669"
},
{
"id": 226617469,
"refreshType": "Scheduled",
"startTime": "2021-06-06T03:01:03.19Z",
"endTime": "2021-06-06T03:04:02.56Z",
"status": "Completed",
"requestId": "4aeaf6e5-cdff-4994-9e20-9b437ec9b43f"
},
{
"id": 226059385,
"refreshType": "Scheduled",
"startTime": "2021-06-05T03:02:02.01Z",
"endTime": "2021-06-05T03:03:53.897Z",
"status": "Completed",
"requestId": "9e13ff9b-b6fc-4fdf-8cd8-a0cd70d4f428"
},
{
"id": 225489417,
"refreshType": "Scheduled",
"startTime": "2021-06-04T03:02:03.457Z",
"endTime": "2021-06-04T03:04:20.213Z",
"status": "Completed",
"requestId": "e738320b-e8b0-44ce-9c37-c8b506aac367"
},
{
"id": 224959539,
"refreshType": "OnDemand",
"startTime": "2021-06-03T04:40:34.723Z",
"endTime": "2021-06-03T04:50:06.517Z",
"status": "Completed",
"requestId": "13764c08-9e17-60e2-7f7c-25312a9fcdc8"
},
{
"id": 224938062,
"refreshType": "OnDemand",
"startTime": "2021-06-03T03:51:38.43Z",
"endTime": "2021-06-03T03:51:41.393Z",
"serviceExceptionJson": "{\"errorCode\":\"ModelRefresh_ShortMessage_ProcessingError\",\"errorDescription\":\"{\\\"error\\\":{\\\"code\\\":\\\"DMTS_OAuthTokenRefreshFailedError\\\",\\\"pbi.error\\\":{\\\"code\\\":\\\"DMTS_OAuthTokenRefreshFailedError\\\",\\\"parameters\\\":{\\\"ConnectionDetails\\\":\\\"{\\\\\\\"kind\\\\\\\":\\\\\\\"SQL\\\\\\\",\\\\\\\"path\\\\\\\":\\\\\\\"srv-xyzabcdefteglobal-ne-prod.database.windows.net;dw-xyzabcdefteGlobal-ne-prod\\\\\\\"}\\\",\\\"CredentialType\\\":\\\"OAuth2\\\",\\\"DatasourceType\\\":\\\"Sql\\\"},\\\"details\\\":[{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"AADSTS500341: The user account cvbnmldkjhg has been deleted from the abcdefghijkl directory. To sign into this application, the account must be added to the directory.\\\\r\\\\nTrace ID: c3e3ef14-0be4-40a2-8234-a1da92b60500\\\\r\\\\nCorrelation ID: d45df754-3474-4184-9860-c302f3a37be5\\\\r\\\\nTimestamp: 2021-06-03 03:51:40Z\\\"}},{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"AADSTS500341: The user account cvbnmldkjhg has been deleted from the abcdefghijkl directory. To sign into this application, the account must be added to the directory.\\\\r\\\\nTrace ID: c3e3ef14-0be4-40a2-8234-a1da92b60500\\\\r\\\\nCorrelation ID: d45df754-3474-4184-9860-c302f3a37be5\\\\r\\\\nTimestamp: 2021-06-03 03:51:40Z\\\"}},{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingHResult\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"-2146233088\\\"}}],\\\"exceptionCulprit\\\":1}}} Table: Edistymät.\"}",
"status": "Failed",
"requestId": "320a53f8-b894-48f0-8e60-eed08bcd9c02"
},
{
"id": 224920821,
"refreshType": "Scheduled",
"startTime": "2021-06-03T03:02:03.623Z",
"endTime": "2021-06-03T03:10:14.277Z",
"serviceExceptionJson": "{\"errorCode\":\"ModelRefresh_ShortMessage_ProcessingError\",\"errorDescription\":\"{\\\"error\\\":{\\\"code\\\":\\\"DMTS_OAuthTokenRefreshFailedError\\\",\\\"pbi.error\\\":{\\\"code\\\":\\\"DMTS_OAuthTokenRefreshFailedError\\\",\\\"parameters\\\":{\\\"ConnectionDetails\\\":\\\"{\\\\\\\"kind\\\\\\\":\\\\\\\"SQL\\\\\\\",\\\\\\\"path\\\\\\\":\\\\\\\"srv-xyzabcdefteglobal-ne-prod.database.windows.net;dw-xyzabcdefteGlobal-ne-prod\\\\\\\"}\\\",\\\"CredentialType\\\":\\\"OAuth2\\\",\\\"DatasourceType\\\":\\\"Sql\\\"},\\\"details\\\":[{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"AADSTS500341: The user account cvbnmldkjhg has been deleted from the abcdefghijkl directory. To sign into this application, the account must be added to the directory.\\\\r\\\\nTrace ID: 0f6ffb9d-8daa-47f6-b628-6fd2bb790400\\\\r\\\\nCorrelation ID: b31c56fc-0f85-49db-b5a8-412441c1a920\\\\r\\\\nTimestamp: 2021-06-03 03:10:13Z\\\"}},{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"AADSTS500341: The user account cvbnmldkjhg has been deleted from the abcdefghijkl directory. To sign into this application, the account must be added to the directory.\\\\r\\\\nTrace ID: 0f6ffb9d-8daa-47f6-b628-6fd2bb790400\\\\r\\\\nCorrelation ID: b31c56fc-0f85-49db-b5a8-412441c1a920\\\\r\\\\nTimestamp: 2021-06-03 03:10:13Z\\\"}},{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingHResult\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"-2146233088\\\"}}],\\\"exceptionCulprit\\\":1}}} Table: mart_safran project_groups.\"}",
"status": "Failed",
"requestId": "5f1410c7-e941-45af-9e62-64195da055d3"
},
{
"id": 224538659,
"refreshType": "OnDemand",
"startTime": "2021-06-02T10:38:28.633Z",
"endTime": "2021-06-02T10:38:38.107Z",
"serviceExceptionJson": "{\"errorCode\":\"ModelRefresh_ShortMessage_ProcessingError\",\"errorDescription\":\"{\\\"error\\\":{\\\"code\\\":\\\"DMTS_OAuthTokenRefreshFailedError\\\",\\\"pbi.error\\\":{\\\"code\\\":\\\"DMTS_OAuthTokenRefreshFailedError\\\",\\\"parameters\\\":{\\\"ConnectionDetails\\\":\\\"{\\\\\\\"kind\\\\\\\":\\\\\\\"SQL\\\\\\\",\\\\\\\"path\\\\\\\":\\\\\\\"srv-xyzabcdefteglobal-ne-prod.database.windows.net;dw-xyzabcdefteGlobal-ne-prod\\\\\\\"}\\\",\\\"CredentialType\\\":\\\"OAuth2\\\",\\\"DatasourceType\\\":\\\"Sql\\\"},\\\"details\\\":[{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"AADSTS500341: The user account cvbnmldkjhg has been deleted from the abcdefghijkl directory. To sign into this application, the account must be added to the directory.\\\\r\\\\nTrace ID: 0269a31d-97bd-415b-80d5-9f5e10e00000\\\\r\\\\nCorrelation ID: e89482b1-59f8-475d-afae-7c38366542a1\\\\r\\\\nTimestamp: 2021-06-02 10:38:34Z\\\"}},{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"AADSTS500341: The user account cvbnmldkjhg has been deleted from the abcdefghijkl directory. To sign into this application, the account must be added to the directory.\\\\r\\\\nTrace ID: 0269a31d-97bd-415b-80d5-9f5e10e00000\\\\r\\\\nCorrelation ID: e89482b1-59f8-475d-afae-7c38366542a1\\\\r\\\\nTimestamp: 2021-06-02 10:38:34Z\\\"}},{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingHResult\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"-2146233088\\\"}}],\\\"exceptionCulprit\\\":1}}} Table: Edistymät.\"}",
"status": "Failed",
"requestId": "184c767d-30f0-46c1-aa25-bd22cc245d99"
},
{
"id": 224528885,
"refreshType": "OnDemand",
"startTime": "2021-06-02T10:11:04.647Z",
"endTime": "2021-06-02T10:11:07.3Z",
"serviceExceptionJson": "{\"errorCode\":\"ModelRefresh_ShortMessage_ProcessingError\",\"errorDescription\":\"{\\\"error\\\":{\\\"code\\\":\\\"DMTS_OAuthTokenRefreshFailedError\\\",\\\"pbi.error\\\":{\\\"code\\\":\\\"DMTS_OAuthTokenRefreshFailedError\\\",\\\"parameters\\\":{\\\"ConnectionDetails\\\":\\\"{\\\\\\\"kind\\\\\\\":\\\\\\\"SQL\\\\\\\",\\\\\\\"path\\\\\\\":\\\\\\\"srv-xyzabcdefteglobal-ne-prod.database.windows.net;dw-xyzabcdefteGlobal-ne-prod\\\\\\\"}\\\",\\\"CredentialType\\\":\\\"OAuth2\\\",\\\"DatasourceType\\\":\\\"Sql\\\"},\\\"details\\\":[{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"AADSTS500341: The user account cvbnmldkjhg has been deleted from the abcdefghijkl directory. To sign into this application, the account must be added to the directory.\\\\r\\\\nTrace ID: b936135f-a6e5-4cc0-9ae2-97dd451c0000\\\\r\\\\nCorrelation ID: 6f6dedbc-232d-431c-aaa0-078fe661d417\\\\r\\\\nTimestamp: 2021-06-02 10:11:06Z\\\"}},{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"AADSTS500341: The user account cvbnmldkjhg has been deleted from the abcdefghijkl directory. To sign into this application, the account must be added to the directory.\\\\r\\\\nTrace ID: b936135f-a6e5-4cc0-9ae2-97dd451c0000\\\\r\\\\nCorrelation ID: 6f6dedbc-232d-431c-aaa0-078fe661d417\\\\r\\\\nTimestamp: 2021-06-02 10:11:06Z\\\"}},{\\\"code\\\":\\\"DM_ErrorDetailNameCode_UnderlyingHResult\\\",\\\"detail\\\":{\\\"type\\\":1,\\\"value\\\":\\\"-2146233088\\\"}}],\\\"exceptionCulprit\\\":1}}} Table: mart_safran project_groups.\"}",
"status": "Failed",
"requestId": "22943f4b-c746-bcab-5aa3-5ca196d20fac"
}
]

[BernatAgullo]

If you want a table with a variable number of columns, maybe you want to rethink your plan as it does not work well in powerbi. You might want to set a limit and then unpivot or something to have a stable number of columns

[Krishanu]

Hi,
Not thinking of keeping variable no.of columns. In case, the columns are not applicable they can be null - like the screenshot from Pat.
Or if all nested values can be captured in seprate rows thats also fine.

[Vera_33]

Hi @Krishanu 

You can combine tables with different headers, paste below M code in Advanced Editor with a blank query, see if it is what you want

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VrbcuPGEf0VlJ41qLn03JCnLWmTOJWNY4t+8XJLNVeJuxRIA+CuFZf+Jn+SH3ODNykSJK5JuWofyFKJJNgzPQd9uvsMgPfvT96P69/G9fhkEscnVcEFN1RITU/7Y03KTWqvR7fz1P84PrkI1ykupgltlwZt55puNLlZ/8wpZ4QqwvSIiqr/k6VQ+ue1darjs7a6ErZkfGuLM3eLdmV6NruZT1O39dqkXxap7b6Lq58Ni8EISISFQAlwJ4lhNJOgkomSGZBSjk/G9d3pY6hcaA7qMKhquXyOaEvLxMtQV7ayklCi7z2gOpu1tiIQrrUkEKgjzgZGPLciJqmjEmoQKlNaYFwPgyo3UHlp7A6oK1tVCVHqfYIKPArLNWA8syeQbCQmSEOSzy77YJiTbBgpYxTgQKSwQapLw14GujRlupK2pHqfmGZtLTfekhh0IKCzJjaLRCgNTLtkNeV2ECmVEsCww5CKJVLWZyozLyMV60RlUGq7D1Klkk0ycKItFwScBGJMTiQHFXl0XMcghpBya62h9MBE5RukvFR2R01a2jKF5qWV+9A3Cy+oZZHEbJDDUQWCICJxOdNsmE7uOaiADAY4DCq7T1RldiQqW9ckJkoq94lqTDZH0dckEzQBrxOWX56J5ZxpsMEF5wehGqOUsvwwqHQD1ZSwK6p0U37xtKg9kFKuJXAPxPvgCJhoiFcUSAYts5TaRXDDSAXlwuqDkFJ732i4ehHo2lRWnJda7ENfoXz2AvFRJhQBBZipUnHiUwg+mSCMHkaqtbAKDlMP1NzTF16uvmtTUQnzkOl/pKM6ZrwLgQgFyF5uKbFGMCKp4xhTCRn4MFIutXmuJn1fn6cbV+8CitJHVVxVIEp4GefK0lRclwL2KkjWs5AYEBsk4hSeEhRHiSSUDMgRLMZ0sMtwzbRm9LAspfo+onpHQVrbyr4jSbYPVA8yBRYtEY4xFEleEk8VEAUe4xl4wKozCFUpLFjKHgZVbdqMKJl9GenKFPqzIvepR+CSyypJEmLOBKzF6CaOBPYgdAr9Wx4GSqUV5jCNRB+oQbojS+U6S6VA4bhPSG1iIiOFsd5m1Eg5ZlSDWJ4cDVHT2CepGUQqwVhgB1ZeuK+8IF/uMWtbqDgtOdunICUtjODUk2QwRwFCwpQVmgTjJVUOdYPSg1DBSivFM+z9yoKEEg8qoJVAicd35OnSVlIU+XvmKRNa4RbGIGmZJoomFIYZJTCXgnHc6QTc1j2DVGDlfaYkfT1S5CPrGwfsBro0BVaKB7o3NZ8nIb39NaR5N5nV/2hn9Wrgb+MxTtU0s+ZsFlP/per/vcMv0x9Xy728uJ413bvUtu4qXf67mQX8OKmv3vajeuPT7RznqQ3NZOliO1XvYby12HyptofDyu94M2D54fzd6OLy+zeL7no0+5Tq9Ur+6iaYAG8fznO6+TD3k/JP9uAad4MMadqnLs5mdZ1Cj/s8dThH+8TfxnTzOvk0wbg/OlY9PnDxwz8fHzp9fGDuuuudE7XNZ/Lr7X+cDzHlLl1NZ95NSZ3IvJnFMrrOedem8gsuavalLevU/SV+eTjib/834vH0d49Xd3LWpJjqbuKmS7o/Ph3LM8+fjDrHhbSzRRPS4KiLX6Zbj9sx8dEZf78r8JdvV1zth/0LQ9oz//KnOqZmervh9ZruTxa4craddOuqe7hctjX/7KaLp0t48+b8YnQhKRXAqmJ0nYoFZmiBym62qLsifPb1zTR++nh9VVy7tvAp1QXmY1+ditzMbooOh6wCc3U9+fhpWsRJg/SbNbdlMZoV7eSqLiZ1N0PDSVu4+Xw6Ca5n5+lq6NrRzaLtcPbCxYgzL83Tg6k20d2kwrgeNS6k4rvzqggiiZRRolGfcM+IApQYLoA4Fp3luNNAdE/Hn82aJk2XK1nOEkHGjPsUgjoRZ2G4/bRGURJwD5KFE9onObAKLHtYKm/mWF+3da9Y1z3685Yhd6dHJhyZ8CpM+PuPqV1Muz+HBIQzUFwIasz9ij9sB6VN1z5bTOfNpLuf9+7urhg5P01V8TZO2u725n//7crl+AGFs+ptz+yhOXVSZEM8SlACJuOOK+HpTylSg/SygQ7vLMFyavhh1/VWomWlWdVXKDlRMdpf2OMPLtYeBc4reDgKnKPAOba1VVujWeXs+zs50bn+TofC/Tw3ROXIvdeWwte0NS9YkP1lAJqNJGCjJ146gw2OA7DAsEMOzPJsW+vrnjgKnCMTXp0J37zAuUHpcNm63Li6wAL9EQN3edXMFvN2L8EjUbEyGjRJFhgB6TKxSXGigFkZHZUyDt/zAimMkoddueIjDJ8wFTelEjv0zsZWmJLRo955VQ9HvXPUO8cut+5yXFknWCRW+4hdSXpiaJTEZpkYTbR/PR3/uMsl3D4a7hmRFjeToGX/FIFLRAdhhFISuGMDqxjocrxY1z046p0jE16dCd+83jnwgg4zELTSkQiaKQEVGHGOS+Ij5yGgiol2+L4ySG7Mc7db/4i+YayiUCrYcQ9ya6vL4+2qV/VwVDdHdXPsaaue5q1QTMhMXP8ICvTPXluXOLa4GEGy8HU9TWWFnn0gXHDsjLijx6LqKKHa5KQUi8D0wCqe62l91VNHdXNkwqsz4ZtXN698NWf5KHIGT4IGRXxwnkjnBJHBMasip9mFpdoZ1x9OPnz4HQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform( Json.Document([Column1]), each Table.PromoteHeaders(Table.Transpose( Record.ToTable(_) )))),
    Custom1 = Table.Combine( #"Added Custom"[Custom]{0})
in
    Custom1

[Krishanu]

Hi, @Vera_33 
It brings to the same position what Pat had shown. I have to go under these records too- see below screenshot and reply to Pat's response for details. Thanks for your help.

[Vera_33]

Hi @Krishanu 

I missed it...you have to do some click then...and I don't see I can go to lower level, simply use a function, but it stops here as the Json is not complete...just give you a little hint

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VrbcuPGEf0VlJ41qLn03JCnLWmTOJWNY4t+8XJLNVeJuxRIA+CuFZf+Jn+SH3ODNykSJK5JuWofyFKJJNgzPQd9uvsMgPfvT96P69/G9fhkEscnVcEFN1RITU/7Y03KTWqvR7fz1P84PrkI1ykupgltlwZt55puNLlZ/8wpZ4QqwvSIiqr/k6VQ+ue1darjs7a6ErZkfGuLM3eLdmV6NruZT1O39dqkXxap7b6Lq58Ni8EISISFQAlwJ4lhNJOgkomSGZBSjk/G9d3pY6hcaA7qMKhquXyOaEvLxMtQV7ayklCi7z2gOpu1tiIQrrUkEKgjzgZGPLciJqmjEmoQKlNaYFwPgyo3UHlp7A6oK1tVCVHqfYIKPArLNWA8syeQbCQmSEOSzy77YJiTbBgpYxTgQKSwQapLw14GujRlupK2pHqfmGZtLTfekhh0IKCzJjaLRCgNTLtkNeV2ECmVEsCww5CKJVLWZyozLyMV60RlUGq7D1Klkk0ycKItFwScBGJMTiQHFXl0XMcghpBya62h9MBE5RukvFR2R01a2jKF5qWV+9A3Cy+oZZHEbJDDUQWCICJxOdNsmE7uOaiADAY4DCq7T1RldiQqW9ckJkoq94lqTDZH0dckEzQBrxOWX56J5ZxpsMEF5wehGqOUsvwwqHQD1ZSwK6p0U37xtKg9kFKuJXAPxPvgCJhoiFcUSAYts5TaRXDDSAXlwuqDkFJ732i4ehHo2lRWnJda7ENfoXz2AvFRJhQBBZipUnHiUwg+mSCMHkaqtbAKDlMP1NzTF16uvmtTUQnzkOl/pKM6ZrwLgQgFyF5uKbFGMCKp4xhTCRn4MFIutXmuJn1fn6cbV+8CitJHVVxVIEp4GefK0lRclwL2KkjWs5AYEBsk4hSeEhRHiSSUDMgRLMZ0sMtwzbRm9LAspfo+onpHQVrbyr4jSbYPVA8yBRYtEY4xFEleEk8VEAUe4xl4wKozCFUpLFjKHgZVbdqMKJl9GenKFPqzIvepR+CSyypJEmLOBKzF6CaOBPYgdAr9Wx4GSqUV5jCNRB+oQbojS+U6S6VA4bhPSG1iIiOFsd5m1Eg5ZlSDWJ4cDVHT2CepGUQqwVhgB1ZeuK+8IF/uMWtbqDgtOdunICUtjODUk2QwRwFCwpQVmgTjJVUOdYPSg1DBSivFM+z9yoKEEg8qoJVAicd35OnSVlIU+XvmKRNa4RbGIGmZJoomFIYZJTCXgnHc6QTc1j2DVGDlfaYkfT1S5CPrGwfsBro0BVaKB7o3NZ8nIb39NaR5N5nV/2hn9Wrgb+MxTtU0s+ZsFlP/per/vcMv0x9Xy728uJ413bvUtu4qXf67mQX8OKmv3vajeuPT7RznqQ3NZOliO1XvYby12HyptofDyu94M2D54fzd6OLy+zeL7no0+5Tq9Ur+6iaYAG8fznO6+TD3k/JP9uAad4MMadqnLs5mdZ1Cj/s8dThH+8TfxnTzOvk0wbg/OlY9PnDxwz8fHzp9fGDuuuudE7XNZ/Lr7X+cDzHlLl1NZ95NSZ3IvJnFMrrOedem8gsuavalLevU/SV+eTjib/834vH0d49Xd3LWpJjqbuKmS7o/Ph3LM8+fjDrHhbSzRRPS4KiLX6Zbj9sx8dEZf78r8JdvV1zth/0LQ9oz//KnOqZmervh9ZruTxa4craddOuqe7hctjX/7KaLp0t48+b8YnQhKRXAqmJ0nYoFZmiBym62qLsifPb1zTR++nh9VVy7tvAp1QXmY1+ditzMbooOh6wCc3U9+fhpWsRJg/SbNbdlMZoV7eSqLiZ1N0PDSVu4+Xw6Ca5n5+lq6NrRzaLtcPbCxYgzL83Tg6k20d2kwrgeNS6k4rvzqggiiZRRolGfcM+IApQYLoA4Fp3luNNAdE/Hn82aJk2XK1nOEkHGjPsUgjoRZ2G4/bRGURJwD5KFE9onObAKLHtYKm/mWF+3da9Y1z3685Yhd6dHJhyZ8CpM+PuPqV1Muz+HBIQzUFwIasz9ij9sB6VN1z5bTOfNpLuf9+7urhg5P01V8TZO2u725n//7crl+AGFs+ptz+yhOXVSZEM8SlACJuOOK+HpTylSg/SygQ7vLMFyavhh1/VWomWlWdVXKDlRMdpf2OMPLtYeBc4reDgKnKPAOba1VVujWeXs+zs50bn+TofC/Tw3ROXIvdeWwte0NS9YkP1lAJqNJGCjJ146gw2OA7DAsEMOzPJsW+vrnjgKnCMTXp0J37zAuUHpcNm63Li6wAL9EQN3edXMFvN2L8EjUbEyGjRJFhgB6TKxSXGigFkZHZUyDt/zAimMkoddueIjDJ8wFTelEjv0zsZWmJLRo955VQ9HvXPUO8cut+5yXFknWCRW+4hdSXpiaJTEZpkYTbR/PR3/uMsl3D4a7hmRFjeToGX/FIFLRAdhhFISuGMDqxjocrxY1z046p0jE16dCd+83jnwgg4zELTSkQiaKQEVGHGOS+Ij5yGgiol2+L4ySG7Mc7db/4i+YayiUCrYcQ9ya6vL4+2qV/VwVDdHdXPsaaue5q1QTMhMXP8ICvTPXluXOLa4GEGy8HU9TWWFnn0gXHDsjLijx6LqKKHa5KQUi8D0wCqe62l91VNHdXNkwqsz4ZtXN698NWf5KHIGT4IGRXxwnkjnBJHBMasip9mFpdoZ1x9OPnz4HQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" =Table.AddColumn(#"Changed Type", "Custom", each List.Transform( Json.Document([Column1]), each Table.PromoteHeaders(Table.Transpose( Record.ToTable(_) )))),
    Custom1 = Table.Combine( #"Added Custom"[Custom]{0}),
    fxExpandJson = (x)=> try Table.PromoteHeaders(Table.Transpose( Record.ToTable(Json.Document(x)))) otherwise x ,
    #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each fxExpandJson([serviceExceptionJson])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"errorCode", "errorDescription"}, {"errorCode", "errorDescription"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Custom", each fxExpandJson([errorDescription]))
in
    #"Added Custom2"

[mahoneypat]

What issue are you having?  I put your JSON code in a text file called ExampleJSON.json and used Get Data with the JSON connector to automatically get this this point.

Pat

[Krishanu]

Thanks Pat @mahoneypat  for responding.

If you see the last four records,  in serviceExceptionJson column it has nested records.

I need to expand them as well and there is no limit in number of nesting, I need to go upto the last level -  as long as the nesting goes and bring the data in separate columns/records in Power Query.

Also, I have some files where this serviceExceptionJson column is not present at all, just like initial set of records in your screenshot. Will I have to do something or Power Query automatically handles this?