Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Krishanu
Helper I
Helper I

Power Query - Json with varying nested structure

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"
}
]

7 REPLIES 7
Anonymous
Not applicable

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

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.

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

 

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.

Krishanu_0-1624278049661.png

 

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

 

Vera_33_0-1624281800819.png

 

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
Employee
Employee

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.

 

mahoneypat_0-1624129641078.png

 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.