Forum Discussion

jdogcisco

Resolver I

8 years ago

Solved

Need help expanding JSON list in column...

I'm pulling in JSON formated data from Redmine.

Source:

I'm able to convert to a table:

Click on List and it gives me the below...

Expand the column to get many of the fields

Many individual field show up (good), some columns have more "record" values which I can expand (good)

But, one column, "custom_fields", contains a list...

If I try to expand to "Extrac Values" I get an error.

If I try to "Expand to New Rows", it works, but then I have a new row for each field, thus, giving me duplicate record entries. That is, the row count increases in the table. For example, in the image below, there is one row for column "id" for each custom field, so I get non-distinct rows...

I don't want to have to work with the above, if possible.

Is it possible to expand the list into new columns instead of rows? Below is what the JSON output looks like...

Here is what the Advanced Editor shows right before trying to expand "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"

How can I get the list expanded into columns instead of rows?

Need Help

Seward12533

8 years ago

jdogcisco What I do in a similar situation is to Pivot those columns. Here is a query of mine as an example. has Custom Field and Value.

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

Before the pivot there are duplicate rows. but after there is a column for each Custom Field with the values for that Field polulated in it.

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"([description]))
in
    #"Invoked Custom Function"

4 Replies

Seward12533

Solution Sage

8 years ago

jdogcisco What I do in a similar situation is to Pivot those columns. Here is a query of mine as an example. has Custom Field and Value.

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

Before the pivot there are duplicate rows. but after there is a column for each Custom Field with the values for that Field polulated in it.

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"([description]))
in
    #"Invoked Custom Function"

Seward12533

Solution Sage

8 years ago

Note if you have HTML Text Fields you woud probably like the custom function to pull out the HTML Tags.

HTML as Text

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

jdogcisco
Resolver I
8 years ago
Seward12533,

I took another look at your suggestion here and this was the correct way to do it. It look me awhile to understand the code, and discovered that this can be configured using the GUI, as well (Pivot Column).

Thank you for pointing me in the correct direction with this one.

jdogcisco
Resolver I
8 years ago
Seward12533 I appreciate the reply. I wasn't able to figure out do what you do, unfortunately. However, I found a solution that works well for me.

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

Thank you

Forum Discussion

Need help expanding JSON list in column...

4 Replies

Related content

Expand column M

Expand list with records to columns

Expand Columns and Rows

API expand column

How to expand column of mixed data