Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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? 😕
Solved! Go to Solution.
Good day lorish,
The steps from #"Expanded issues.fields.issuetype" to #"Expanded issues.fields.status" each are of the form
#"Expanded issues.fields.fixVersions" = Table.ExpandRecordColumn(#"Expanded issues.fields ...etc
#"Expanded issues.fields.fixVersions" = Table.ExpandRecordColumn(#"Expanded issues.fields.issuetype ...etc
Hope this helps
Good day lorish,
The steps from #"Expanded issues.fields.issuetype" to #"Expanded issues.fields.status" each are of the form
#"Expanded issues.fields.fixVersions" = Table.ExpandRecordColumn(#"Expanded issues.fields ...etc
#"Expanded issues.fields.fixVersions" = Table.ExpandRecordColumn(#"Expanded issues.fields.issuetype ...etc
Hope this helps