Necesito ayuda para expandir la lista JSON en la columna…

Estoy extrayendo datos con formato JSON de Redmine.

Fuente:

Puedo convertir a una tabla:

Haga clic en Lista y me da lo siguiente…

Expanda la columna para obtener muchos de los campos

Aparecen muchos campos individuales (bien), algunas columnas tienen más valores de «registro» que puedo expandir (bien)

Pero, una columna, «campos_personalizados», contiene una lista…

Si intento expandirme a «Extrac Values», aparece un error.

Si trato de «Expandir a nuevas filas», funciona, pero luego tengo una nueva fila para cada campo, lo que me da entradas de registro duplicadas. Es decir, el número de filas aumenta en la tabla. Por ejemplo, en la imagen a continuación, hay una fila para la columna «id» para cada campo personalizado, por lo que obtengo filas no distintas…

No quiero tener que trabajar con lo anterior, si es posible.

¿Es posible expandir la lista en nuevas columnas en lugar de filas? A continuación se muestra el aspecto de la salida JSON…

Esto es lo que muestra el Editor avanzado justo antes de intentar expandir «custom_fields»

let
    Source = Json.Document(Web.Contents("https://mysupport.cpacketnetworks.com/issues.json?offset=0&limit=100")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{0}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "project", "tracker", "status", "priority", "author", "subject", "description", "start_date", "done_ratio", "custom_fields", "created_on", "updated_on", "assigned_to"}, {"id", "project", "tracker", "status", "priority", "author", "subject", "description", "start_date", "done_ratio", "custom_fields", "created_on", "updated_on", "assigned_to"}),
    #"Expanded project" = Table.ExpandRecordColumn(#"Expanded Column1", "project", {"id", "name"}, {"project.id", "project.name"}),
    #"Expanded tracker" = Table.ExpandRecordColumn(#"Expanded project", "tracker", {"id", "name"}, {"tracker.id", "tracker.name"}),
    #"Expanded status" = Table.ExpandRecordColumn(#"Expanded tracker", "status", {"id", "name"}, {"status.id", "status.name"}),
    #"Expanded priority" = Table.ExpandRecordColumn(#"Expanded status", "priority", {"id", "name"}, {"priority.id", "priority.name"}),
    #"Expanded author" = Table.ExpandRecordColumn(#"Expanded priority", "author", {"id", "name"}, {"author.id", "author.name"}),
    #"Expanded assigned_to" = Table.ExpandRecordColumn(#"Expanded author", "assigned_to", {"id", "name"}, {"assigned_to.id", "assigned_to.name"})
in
    #"Expanded assigned_to"

¿Cómo puedo expandir la lista en columnas en lugar de filas?

@jdogcisco Lo que hago en una situación similar es girar esas columnas. Aquí hay una consulta mía como ejemplo. tiene campo personalizado y valor.

    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Custom Field"]), "Custom Field", "value"),

Antes del pivote hay filas duplicadas. pero después hay una columna para cada campo personalizado con los valores de ese campo en él.

let
    Source = Web.Contents
xxxxxxx - removed xxxxx 
),
convertToJson = Json.Document(Source),
    data = convertToJson[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "accountId", "title", "description", "briefDescription", "parentIds", "superParentIds", "responsibleIds", "status", "importance", "createdDate", "updatedDate", "completedDate", "dates", "scope", "authorIds", "customStatusId", "hasAttachments", "attachmentCount", "permalink", "priority", "superTaskIds", "subTaskIds", "metadata", "customFields", "sharedIds", "dependencyIds"}, {"id", "accountId", "title", "description", "briefDescription", "parentIds", "superParentIds", "responsibleIds", "status", "importance", "createdDate", "updatedDate", "completedDate", "dates", "scope", "authorIds", "customStatusId", "hasAttachments", "attachmentCount", "permalink", "priority", "superTaskIds", "subTaskIds", "metadata", "customFields", "sharedIds", "dependencyIds"}),
    #"Parsed Date" = Table.TransformColumns(#"Expanded Column1",{{"createdDate", each Date.From(DateTimeZone.From(_)), type date}, {"updatedDate", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Parsed Date2" = Table.TransformColumns(#"Parsed Date",{{"completedDate", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Extracted parentIds" = Table.TransformColumns(#"Parsed Date2", {"parentIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted superParentIds" = Table.TransformColumns(#"Extracted parentIds", {"superParentIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Keep only SSP Items" = Table.SelectRows(#"Extracted superParentIds", each [parentIds] = "IEAA7BMYI4EKI6VT"),
    #"Removed parent and superParent id Columns" = Table.RemoveColumns(#"Keep only SSP Items",{"parentIds", "superParentIds", "accountId"}),
    #"Extracted responsibleIds" = Table.TransformColumns(#"Removed parent and superParent id Columns", {"responsibleIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Expanded dates" = Table.ExpandRecordColumn(#"Extracted responsibleIds", "dates", {"type", "duration", "start", "due", "workOnWeekends"}, {"type", "duration", "start", "due", "workOnWeekends"}),
    #"Extracted metadata" = Table.TransformColumns(#"Expanded dates", {"metadata", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Expanded customFields" = Table.ExpandListColumn(#"Extracted metadata", "customFields"),
    #"Expanded customFields1" = Table.ExpandRecordColumn(#"Expanded customFields", "customFields", {"id", "value"}, {"id.1", "value"}),
    #"Parsed Date1" = Table.TransformColumns(#"Expanded customFields1",{{"start", each Date.From(DateTimeZone.From(_)), type date}, {"due", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Extracted authorIds" = Table.TransformColumns(#"Parsed Date1", {"authorIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted superTaskIds" = Table.TransformColumns(#"Extracted authorIds", {"superTaskIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted subTaskIds" = Table.TransformColumns(#"Extracted superTaskIds", {"subTaskIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted subTaskIds", each true),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"id.1", type text}, {"value", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"No Custom Fields",Replacer.ReplaceValue,{"id.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"No Custom Values",Replacer.ReplaceValue,{"value"}),
    #"Merge Workflow Name" = Table.NestedJoin(#"Replaced Value1",{"customStatusId"},#"JSON Workflows",{"Step.id"},"JSON Workflows",JoinKind.LeftOuter),
    #"Expanded JSON Workflows" = Table.ExpandTableColumn(#"Merge Workflow Name", "JSON Workflows", {"Step.name"}, {"Step.name"}),
    #"Rename Step.id to Worfklow Step" = Table.RenameColumns(#"Expanded JSON Workflows",{{"Step.name", "Workflow Step"}}),    
    #"Merge Custom Field Names" = Table.NestedJoin( #"Rename Step.id to Worfklow Step",{"id.1"},#"JSON Custom Fields",{"id"},"JSON Custom Fields",JoinKind.LeftOuter),
    #"Expanded JSON Custom Fields" = Table.ExpandTableColumn(#"Merge Custom Field Names", "JSON Custom Fields", {"title"}, {"Custom Field"}),
    #"Remove Null from Custom Field Name" = Table.ReplaceValue(#"Expanded JSON Custom Fields",null,"",Replacer.ReplaceValue,{"Custom Field"}),
    #"Sorted Rows" = Table.Sort(#"Remove Null from Custom Field Name",{{"id", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"id.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Custom Field"]), "Custom Field", "value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Delete Me"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Media Design Completed", type date}, {"Final Complete", type date}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Test", each #"HTML as Text"(https://community.powerbi.com/t5/Desktop/Need-help-expanding-JSON-list-in-column/m-p/467072
))
in
    #"Invoked Custom Function"

@ Seward12533 Agradezco la respuesta. Desafortunadamente, no pude descifrar lo que haces. Sin embargo, encontré una solución que funciona bien para mí.

http://community.powerbi.com/t5/Integrations-with-Files-and/Multi-Dimensional-Json-file-to-Table/td-…

Gracias

@jdogcisco Lo que hago en una situación similar es girar esas columnas. Aquí hay una consulta mía como ejemplo. tiene campo personalizado y valor.

    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Custom Field"]), "Custom Field", "value"),

Antes del pivote hay filas duplicadas. pero después hay una columna para cada campo personalizado con los valores de ese campo en él.

let
    Source = Web.Contents
xxxxxxx - removed xxxxx 
),
convertToJson = Json.Document(Source),
    data = convertToJson[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "accountId", "title", "description", "briefDescription", "parentIds", "superParentIds", "responsibleIds", "status", "importance", "createdDate", "updatedDate", "completedDate", "dates", "scope", "authorIds", "customStatusId", "hasAttachments", "attachmentCount", "permalink", "priority", "superTaskIds", "subTaskIds", "metadata", "customFields", "sharedIds", "dependencyIds"}, {"id", "accountId", "title", "description", "briefDescription", "parentIds", "superParentIds", "responsibleIds", "status", "importance", "createdDate", "updatedDate", "completedDate", "dates", "scope", "authorIds", "customStatusId", "hasAttachments", "attachmentCount", "permalink", "priority", "superTaskIds", "subTaskIds", "metadata", "customFields", "sharedIds", "dependencyIds"}),
    #"Parsed Date" = Table.TransformColumns(#"Expanded Column1",{{"createdDate", each Date.From(DateTimeZone.From(_)), type date}, {"updatedDate", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Parsed Date2" = Table.TransformColumns(#"Parsed Date",{{"completedDate", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Extracted parentIds" = Table.TransformColumns(#"Parsed Date2", {"parentIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted superParentIds" = Table.TransformColumns(#"Extracted parentIds", {"superParentIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Keep only SSP Items" = Table.SelectRows(#"Extracted superParentIds", each [parentIds] = "IEAA7BMYI4EKI6VT"),
    #"Removed parent and superParent id Columns" = Table.RemoveColumns(#"Keep only SSP Items",{"parentIds", "superParentIds", "accountId"}),
    #"Extracted responsibleIds" = Table.TransformColumns(#"Removed parent and superParent id Columns", {"responsibleIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Expanded dates" = Table.ExpandRecordColumn(#"Extracted responsibleIds", "dates", {"type", "duration", "start", "due", "workOnWeekends"}, {"type", "duration", "start", "due", "workOnWeekends"}),
    #"Extracted metadata" = Table.TransformColumns(#"Expanded dates", {"metadata", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Expanded customFields" = Table.ExpandListColumn(#"Extracted metadata", "customFields"),
    #"Expanded customFields1" = Table.ExpandRecordColumn(#"Expanded customFields", "customFields", {"id", "value"}, {"id.1", "value"}),
    #"Parsed Date1" = Table.TransformColumns(#"Expanded customFields1",{{"start", each Date.From(DateTimeZone.From(_)), type date}, {"due", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Extracted authorIds" = Table.TransformColumns(#"Parsed Date1", {"authorIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted superTaskIds" = Table.TransformColumns(#"Extracted authorIds", {"superTaskIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted subTaskIds" = Table.TransformColumns(#"Extracted superTaskIds", {"subTaskIds", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted subTaskIds", each true),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"id.1", type text}, {"value", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"No Custom Fields",Replacer.ReplaceValue,{"id.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"No Custom Values",Replacer.ReplaceValue,{"value"}),
    #"Merge Workflow Name" = Table.NestedJoin(#"Replaced Value1",{"customStatusId"},#"JSON Workflows",{"Step.id"},"JSON Workflows",JoinKind.LeftOuter),
    #"Expanded JSON Workflows" = Table.ExpandTableColumn(#"Merge Workflow Name", "JSON Workflows", {"Step.name"}, {"Step.name"}),
    #"Rename Step.id to Worfklow Step" = Table.RenameColumns(#"Expanded JSON Workflows",{{"Step.name", "Workflow Step"}}),    
    #"Merge Custom Field Names" = Table.NestedJoin( #"Rename Step.id to Worfklow Step",{"id.1"},#"JSON Custom Fields",{"id"},"JSON Custom Fields",JoinKind.LeftOuter),
    #"Expanded JSON Custom Fields" = Table.ExpandTableColumn(#"Merge Custom Field Names", "JSON Custom Fields", {"title"}, {"Custom Field"}),
    #"Remove Null from Custom Field Name" = Table.ReplaceValue(#"Expanded JSON Custom Fields",null,"",Replacer.ReplaceValue,{"Custom Field"}),
    #"Sorted Rows" = Table.Sort(#"Remove Null from Custom Field Name",{{"id", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"id.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Custom Field"]), "Custom Field", "value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Delete Me"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Media Design Completed", type date}, {"Final Complete", type date}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Test", each #"HTML as Text"(https://community.powerbi.com/t5/Desktop/Need-help-expanding-JSON-list-in-column/m-p/467072
))
in
    #"Invoked Custom Function"

@Seward12533,

Eché otro vistazo a su sugerencia aquí y esta fue la forma correcta de hacerlo. Me tomó un tiempo entender el código y descubrí que esto también se puede configurar usando la GUI (columna pivote).

Gracias por señalarme en la dirección correcta con este.

Tenga en cuenta que si tiene campos de texto HTML, probablemente le gustaría la función personalizada para extraer las etiquetas HTML.

HTML como texto

let
    Source = (HTML as text) => let
    Source = Text.From(HTML),
    SplitAny = Text.SplitAny(Source,"<>"),
    ListAlternate = List.Alternate(SplitAny,1,1,1),
    ListSelect = List.Select(ListAlternate, each _<>""),
    TextCombine = Text.Combine(ListSelect, "")
in
    TextCombine
in
    Source

Entradas relacionadas

Deja un comentario Cancelar respuesta