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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors