Forum Discussion
JSON list object, Extractig Multiple Entries from field
I'm currently trying to pull information which can have multiple entries in a list format.
My component field is a multivalued field which can contain multiple different tags. With my power BI query I am able to interpret this but only 1 entry, but if a row has more than 1 entry, ideally I would need all 3 entries to be available.
| Key | Component1 | Component2 | Component3 |
| 1 | Paper | Invoice | |
| 2 | Paper | Payment | Person |
| 3 | Payment | ||
| 4 | Person |
So, in the above key 2 would have entries with Paper Payment Person
I'm looking to import the whole array to later use in a word cloud style analysis, so I'm not adverse to having a table created with the entries as below, but ideally I just need all the data and not just the first entry, which my query returns now.
| Key | Paper | Invoice | Payment | Person | |
| 1 | True | True | |||
| 2 | True | True | True | ||
| 3 | True | True | |||
| 4 | True |
I've spliced my query below:
let
JQLString = "?jql=project%20%3D%20MS%20AND%20created%20>%3D%202018-03-01",
FieldString = "&fields=summary,created,resolutiondate,components",
APSource = FetchPages(URL, 50,JQLString,FieldString),
#"Expanded column1" = Table.ExpandRecordColumn(APSource, "Column1",{"key","fields"},{"Key","fields"}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Expanded column1", "fields",{"created","resolutiondate","components"},{"created","resolved","components"}),
//This is the element which only returns the first entered component/list
#"Expanded components" = Table.ExpandListColumn(#"Expanded fields", "components"),
#"Expanded components1" = Table.ExpandRecordColumn(#"Expanded components", "components", {"name"}, {"components.name"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded components1", "created", Splitter.SplitTextByEachDelimiter({"T"}, QuoteStyle.Csv, false), {"created.1", "created.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"created.1", type date}, {"created.2", type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"created.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "resolved", Splitter.SplitTextByEachDelimiter({"T"}, QuoteStyle.Csv, false), {"resolved.1", "resolved.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"resolved.1", type date}, {"resolved.2", type time}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"resolved.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"created.1", "Created"}, {"resolved.1", "Resolved"}})
in
#"Renamed Columns"
Below is what is returned by using the rest browser
{
"expand": "renderedFields,names,schema,operations,editmeta,changelog,versionedRepresentations",
"id": "169956",
"self": "http://vmsrv:8080/rest/api/2/issue/169956",
"key": "MS-37090",
"fields": {
"components": [
{
"self": "http://vmsrv:8080/rest/api/2/component/10629",
"id": "10629",
"name": "Front Office"
},
{
"self": "http://vmsrv:8080/rest/api/2/component/10634",
"id": "10634",
"name": "Import"
}
]
}
}
2 Replies
- PhilipTreacySuper User
Hi Anonymous
Can you post some of the actual JSON?
Phil
- AnonymousNot applicable
Thanks for the comment Philip, I've added a sample of the returned values by the rest browser