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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
Microsoft Employee
Microsoft 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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.