cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Power Query : Json con estructura anidada variable

hola

Tengo un json con estructura variable y anidada (abajo), que tengo que cargar en columnas individuales en Power Query hasta todos los niveles posibles. Estoy luchando con esto, por favor ayuda.


json de ejemplo ->
[
{
"id": 232803570,
"refreshType": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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": "Programado",
"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":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: La cuenta de usuario cvbnmldkjhg se ha eliminado del directorio abcdefghijkl. Para iniciar sesión en esta aplicación, la cuenta debe agregarse al directorio.\\r\\nDÚcudo de red: c3e3ef14-0be4-40a2-8234-a1da92b60500\\r\nDút de correlación: d45df754-3474-4184-9860-c302f3a37be5\\r\\"\"""""DM_ErrorDetailNameCode_UnderlyingErrorMessage\",\"detail\":{\"type\":51:\"value\":\":"AADSTS500341: La cuenta de usuario cvbnmldkjhg se ha eliminado del directorio abcdefghijkl. Para iniciar sesión en esta aplicación, la cuenta debe agregarse al directorio.\\r\\nDÚdito de red: c3e3ef14-0be4-40a2-8234-a1da92b60500\\r\\nDúdr. de correlación: d45df754-3474-4184-9860-c302f3a37be5\\r\\nTiesamp: 2021-06-03 03:51:40Z\"}},{\"code\":\"DM_ErrorDetailNameCode_UnderlyingHResult\",\"detail\":{\"type\":1,\"value\":\"-2146233088\"}}],\"exceptionCulprit\":1}}} Tabla: Edistymät."}",
"estado": "Error",
"requestId": "320a53f8-b894-48f0-8e60-eed08bcd9c02"
},
{
"id": 224920821,
"refreshType": "Programado",
"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":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: La cuenta de usuario cvbnmldkjhg se ha eliminado del directorio abcdefghijkl. Para iniciar sesión en esta aplicación, la cuenta debe agregarse al directorio.\\r\\nD ID de red: 0f6ffb9d-8daa-47f6-b628-6fd2bb790400\\r\\nDIS de correlación: b31c56fc-0f85-49db-b5a8-412441c1a920\\r\\""Timestamp: 2021-06-03 03:13Z\"}},{\"code\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\",\"detail\":{\"type\":1,\"value":\"AADSTS500341: La cuenta de usuario cvbnmldkjhg se ha eliminado del directorio abcdefghijkl. Para iniciar sesión en esta aplicación, la cuenta debe agregarse al directorio.\\r\\nDÚcudo de red: 0f6ffb9d-8daa-47f6-b628-6fd2bb790400\\r\\nDúdito de correlación: b31c56fc-0f85-49db-b5a8-412441c1a920\\r\nTiesumbre: 2021-06 -03 03:10:13Z\"}},{\"code\":\"DM_ErrorDetailNameCode_UnderlyingHResult\",\"detail\":{\"type\":1,\"value\":\"-2146233088\"}}],\"exceptionCulprit\":1}}} Tabla: mart_safran project_groups."}",
"estado": "Error",
"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":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: La cuenta de usuario cvbnmldkjhg se ha eliminado del directorio abcdefghijkl. Para iniciar sesión en esta aplicación, la cuenta debe agregarse al directorio.\\r\\nD. de traza: 0269a31d-97bd-415b-80d5-9f5e10e00000\\r\\nD. de correlación: e89482b1-59f8-475d-afae-7c38366542a1\\r\\nTimestamp: 2021-06-02 10:38:34Z\"}},{\"code\":\"DM_ErrorDetailNameCode_UnderlyingErrorMessage\",\"detail\":{\"type\":1,\"value\":\"AADSTS500341: La cuenta de usuario cvbnmldkjhg se ha eliminado del directorio abcdefghijkl. Para iniciar sesión en esta aplicación, la cuenta debe agregarse al directorio.\\r\\nDÚcudo de traza: 0269a31d-97bd-415b-80d5-9f5e10e00000\\r\\nDÚdito de correlación: e89482b1-59f8-475d-afae-7c38366542a1\\r\nTiesamp: 2021-06-02 10:38:34Z\"}},{\"code\":\"DM_ErrorDetailNameCode_UnderlyingHResult\",\"detail\":{\"type\":1,\"value\":\"-2146233088\"}}],\"exceptionCulprit\":1}}} Tabla: Edistymät."}",
"estado": "Error",
"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":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: La cuenta de usuario cvbnmldkjhg se ha eliminado del directorio abcdefghijkl. Para iniciar sesión en esta aplicación, la cuenta debe agregarse al directorio.\\r\\nSÍdito de red: b936135f-a6e5-4cc0-9ae2-97dd451c0000\\r\\nNDÚt de correlación: 6f6dedbc-232d-431c-aaa0-078fe661d417\\\"\""""DM_ErrorDetailNameCode_UnderlyingErrorMessage",\"detail\":{\"type"type":11:06Z\"}},{"code\":"AADS500341: La cuenta de usuario cvbnmldkjhg se ha eliminado del directorio abcdefghijkl. Para iniciar sesión en esta aplicación, la cuenta debe agregarse al directorio.\\r\\nDÚdito de traza: b936135f-a6e5-4cc0-9ae2-97dd451c0000\\r\\nDIdilo de correlación: 6f6dedbc-232d-431c-aaa0-078fe661d417\\r\\nTiempo: 2021-06-2021-06-002 10:11:06Z\"}},{\"code\":\"DM_ErrorDetailNameCode_UnderlyingHResult\",\"detail\":{\"type\":1,\"value\":\"-2146233088\"}}],\"exceptionCulprit\":1}}} Tabla: mart_safran project_groups."}",
"estado": "Error",
"requestId": "22943f4b-c746-bcab-5aa3-5ca196d20fac"
}
]

7 REPLIES 7
Syndicate_Admin
Administrator
Administrator

Si desea una tabla con un número variable de columnas, tal vez desee repensar su plan, ya que no funciona bien en powerbi. Es posible que desee establecer un límite y, a continuación, quitar la dinamización o algo así para tener un número estable de columnas

hola
No pensar en mantener la variable no.de columnas. En caso de que las columnas no sean aplicables, pueden ser nulas, como la captura de pantalla de Pat.
O si todos los valores anidados se pueden capturar en filas seprate, eso también está bien.

No @Krishanu

Puede combinar tablas con diferentes encabezados, pegar debajo del código M en el Editor avanzado con una consulta en blanco, vea si es lo que desea

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

Hola, @Vera_33
Lleva a la misma posición lo que Pat había mostrado. Tengo que ir bajo estos registros también- ver a continuación captura de pantalla y responder a la respuesta de Pat para más detalles. Gracias por su ayuda.

Krishanu_0-1624278049661.png

No @Krishanu

Me lo perdí... tienes que hacer un poco de clic entonces... y no veo que pueda ir al nivel inferior, simplemente use una función, pero se detiene aquí ya que el Json no está completo ... sólo darle una pequeña pista

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"

Syndicate_Admin
Administrator
Administrator

¿Qué problema estás teniendo? Puse su código JSON en un archivo de texto llamado ExampleJSON.json y usé Get Data con el conector JSON para obtener automáticamente este punto.

mahoneypat_0-1624129641078.png

palmadita

Gracias Pat @mahoneypat por responder.

Si ve los últimos cuatro registros, en la columna serviceExceptionJson tiene registros anidados.

Necesito expandirlos también y no hay límite en el número de anidamientos, necesito subir al último nivel, siempre y cuando el anidamiento vaya y traer los datos en columnas / registros separados en Power Query.

Además, tengo algunos archivos donde esta columna serviceExceptionJson no está presente en absoluto, al igual que el conjunto inicial de registros en la captura de pantalla. ¿Tendré que hacer algo o Power Query se encarga automáticamente de esto?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors