Forum Discussion
JSON column is not expandable
Hi all,
hope to find help here, I´m totally new to PBI and JSON and have an issue with expanding some columns in PBI Desktop (and Service as well...)
My source seems to be a JSON file which I load via an API interface from a JIRA (confluence) instance in my company.
I have the following issue with one column from the very beginning of the query. The column "security" is in Jira a dropdown selection, so it should be a kind of list or record entry in PBI so I can expand it and get the values.
But from the beginn on of my query there is no option in this column in PBI to expand. If I´m not wrong, PBI defines regarding the first xxx entries in a column which type it is? In this column are very few entries, so I think its getting the "wrong" content type.
So how can I "say" the query that this column needs to be expanded?
Sorry for my really dummy description...
the query looks like this at the beginning, this is what PBI does automatically until the step "choose column":
let
Source = Json.Document(Web.Contents("https://XXXX.XXXXX.com/rest/api/2/search?jql=filter=XXXX&maxResults=3000")),
#"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", {[...],"security", [...]"}),
#"Choose columns" = Table.SelectColumns(#"Expanded issues.fields",{[...],"issues.fields.security", [...]})
in
#"Choose columns"
And in the table view the column looks like this, here is the expand option missing:
Thanks for any tips and help,
BR primlchen
Got it.
Simply added at the end of the query the syntax:
#"Expanded Security Level" = Table.ExpandRecordColumn(#"Removed Columns", "Security Level", {"name"}, {"Security Level"})
in
#"Expanded Security Level"Works now. 😁
3 Replies
Hi primlchen ,
usually, the method in this blogpost should work for columns of mixed types: How to expand a column that cannot be expanded in Power BI and Power Query in Excel – The BIccountant
But in your example you actually have an empty column. Could it be that you haven't imported all rows yet?Hi ImkeF
thanks for your reply. I´ve already checked the blogpost you´ve mentioned, in my case there are lots of columns (about 100) with mixed types, so I checked also this post https://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-query/.
By checking the code there it is already the code what PBI is doing automatically:
let
Source = Json.Document(Web.Contents("https://xxxx.xxxxxxx.com/rest/api/2/search?jql=filter=26502&maxResults=3000")),
#"Converted to table" = Table.FromRecords({Source})
in
#"Converted to table"So most of the needed columns are expandable, only this one single column I need as well isn´t.
I´ve filtered a specific issue where I know there is an entry in the security level column and look what happens:
So I guess its regarding this:
But still the problem: I can expand the column when filtered rows, but then I can´t remove the filter from the list. So still the issue that I don´t know how to "tell" PBI that there is this specific column to expand.
With the syntax in the mentioned blog post I don´t know how to add it to the existing one with over 100 columns...
Is there anywhere an option to expand e.g. the number of columns PBI uses to profile the columns?
thanks and BR
Primlchen
Got it.
Simply added at the end of the query the syntax:
#"Expanded Security Level" = Table.ExpandRecordColumn(#"Removed Columns", "Security Level", {"name"}, {"Security Level"})
in
#"Expanded Security Level"Works now. 😁
