Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 Component1Component2Component3
1PaperInvoice 
2PaperPaymentPerson
3EmailPayment 
4EmailPerson 

 

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 PaperEmailInvoicePaymentPerson
1True True  
2True  TrueTrue
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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks for the comment Philip, I've added a sample of the returned values by the rest browser