Query successfully expands the "Record" values into columns and then collapses them again

[lorish]

Hello everyone 🙂

Thank you for your help. I really do appreciate it!

There is a problem with my power query M. It's a large JSON, which i do fold up into seperate columns with that query. It looks like this:

let
    Source = Json.Document(Web.Contents("MYURLHERE&fields=created,summary,status,components,issuetype,fixVersions,customfield_10106,priority,customfield_10100,assignee,updated,reporter,customfield_10600,customfield_10206,customfield_11001,customfield_10108,customfield_10104&maxResults=10000", [Headers=[Authorization="Bearer "&GetToken()]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded issues" = Table.ExpandListColumn(#"Converted to Table", "issues"),
    #"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"expand", "id", "self", "key", "fields"}, {"issues.expand", "issues.id", "issues.self", "issues.key", "issues.fields"}),
    #"Expanded issues.fields" = Table.ExpandRecordColumn(#"Expanded issues1", "issues.fields", {"created","summary","status","components","issuetype","fixVersions","customfield_10106","priority","customfield_10100","assignee","updated","reporter","customfield_10600","customfield_10206","customfield_11001","customfield_10108","customfield_10104"}, {"issues.fields.created","issues.fields.summary","issues.fields.status","issues.fields.components","issues.fields.issuetype","issues.fields.fixVersions","issues.fields.customfield_10106","issues.fields.priority","issues.fields.customfield_10100","issues.fields.assignee","issues.fields.updated","issues.fields.reporter","issues.fields.customfield_10600","issues.fields.customfield_10206","issues.fields.customfield_11001","issues.fields.customfield_10108","issues.fields.customfield_10104"}),
    #"Expanded issues.fields.issuetype" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.issuetype", {"self", "id", "description", "iconUrl", "name", "subtask"}, {"issues.fields.issuetype.self", "issues.fields.issuetype.id", "issues.fields.issuetype.description", "issues.fields.issuetype.iconUrl", "issues.fields.issuetype.name", "issues.fields.issuetype.subtask"}),  
    #"Expanded issues.fields.fixVersions" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.fixVersions", {"self", "id", "description", "name", "archived", "released", "releaseDate"}, {"issues.fields.fixVersions.self", "issues.fields.fixVersions.id", "issues.fields.fixVersions.description", "issues.fields.fixVersions.name", "issues.fields.fixVersions.archived", "issues.fields.fixVersions.released", "issues.fields.fixVersions.releaseDate"}),
    #"Expanded issues.fields.priority" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.priority", {"self", "iconUrl", "name", "id"}, {"issues.fields.priority.self", "issues.fields.priority.iconUrl", "issues.fields.priority.name", "issues.fields.priority.id"}),
    #"Expanded issues.fields.assignee" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.assignee", {"self", "name", "key", "emailAddress", "avatarUrls"}, {"issues.fields.assignee.self", "issues.fields.assignee.name", "issues.fields.assignee.key", "issues.fields.assignee.emailAddress", "issues.fields.assignee.avatarUrls"}),
    #"Expanded issues.fields.reporter" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.reporter", {"self", "name", "key", "emailAddress", "avatarUrls"}, {"issues.fields.reporter.self", "issues.fields.reporter.name", "issues.fields.reporter.key", "issues.fields.reporter.emailAddress", "issues.fields.reporter.avatarUrls"}),
    #"Expanded issues.fields.customfield_10600" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.customfield_10600", {"self", "value", "id", "disabled"}, {"issues.fields.customfield_10600.self", "issues.fields.customfield_10600.value", "issues.fields.customfield_10600.id", "issues.fields.customfield_10600.disabled"}),
    #"Expanded issues.fields.customfield_10206" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.customfield_10206", {"self", "value", "id", "disabled"}, {"issues.fields.customfield_10206.self", "issues.fields.customfield_10206.value", "issues.fields.customfield_10206.id", "issues.fields.customfield_10206.disabled"}),
    #"Expanded issues.fields.customfield_11001" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.customfield_11001", {"self", "value", "id", "disabled"}, {"issues.fields.customfield_11001.self", "issues.fields.customfield_11001.value", "issues.fields.customfield_11001.id", "issues.fields.customfield_11001.disabled"}),
    #"Expanded issues.fields.status" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.status", {"self", "description", "iconUrl", "name", "id", "statusCategory"}, {"issues.fields.status.self", "issues.fields.status.description", "issues.fields.status.iconUrl", "issues.fields.status.name", "issues.fields.status.id", "issues.fields.status.statusCategory"}),
    #"Expanded issues.fields.status.statusCategory" = Table.ExpandRecordColumn(#"Expanded issues.fields.status", "issues.fields.status.statusCategory", {"self", "id", "key", "colorName", "name"}, {"issues.fields.status.statusCategory.self", "issues.fields.status.statusCategory.id", "issues.fields.status.statusCategory.key", "issues.fields.status.statusCategory.colorName", "issues.fields.status.statusCategory.name"}),
    #"Changed" = Table.TransformColumnTypes(#"Expanded issues.fields.status.statusCategory",{"expand", type text})
in
    #"Changed"

The whole query works pretty well. The steps with an Table.ExpandRecordColumn do expand the "Record".

BUT:

Only the "issues.fields.status.statusCategory" stays extended after all the steps in the Query... All the others steps where i expand these records successfully, fold back away, when the next step is taking place.

Example:

    #"Expanded issues.fields.issuetype" = Table.ExpandRecordColumn(#"Expanded issues.fields", "issues.fields.issuetype", {"self", "id", "description", "iconUrl", "name", "subtask"}, {"issues.fields.issuetype.self", "issues.fields.issuetype.id", "issues.fields.issuetype.description", "issues.fields.issuetype.iconUrl", "issues.fields.issuetype.name", "issues.fields.issuetype.subtask"}),  

This one (issues.fields.issuetype does expand successfully when in progress.

When i click to the next Step "issues.fields.fixVersions", the successfully expanded Record of "issues.fields.issuetype" folds back away, and the Record of "issues.field.fixVersions" expands successfully.

 In the end, there's only the last Query "issues.fields.status.statusCategory" which stays extended. All the others are fold back.

What the hell am i doing wrong? 😕

[collinsg · Accepted solution]

Good day lorish,

The steps from #"Expanded issues.fields.issuetype" to #"Expanded issues.fields.status" each are of the form 

Table.ExpandRecordColumn with the first parameter being the table they expand. In each case the table they expand is the table created by the step #"Expanded issues.fields". Therefore each step is taking that table and expanding part of it afresh each time. Instead each step should expand the table from the previous step. For example change

#"Expanded issues.fields.fixVersions" = Table.ExpandRecordColumn(#"Expanded issues.fields ...etc

to

#"Expanded issues.fields.fixVersions" = Table.ExpandRecordColumn(#"Expanded issues.fields.issuetype ...etc

Hope this helps