JSON: Expand nested columns with also NULL entries leads always to error

Hi all,

I need your help please, I was searching for days now for the right solution but I couldn´t find out how to solve it. And I´m totally new to PBI and Query.

Tried out several instructions from: https://www.thebiccountant.com/?s=json&submit=Search

https://www.edureka.co/community/40467/replace-null-values-custom-values-power-power-query-editor

https://social.technet.microsoft.com/Forums/en-US/6fc2c988-a93d-4938-962e-82f4a1676dd8/create-a-list-of-commaseparated-values-from-a-field-of-a-list-of-records?forum=powerquery

and also https://community.powerbi.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&q=we%20cannot%20convert%20the%20value%20null%20to%20type%20list

For some of my previous issues I found the right way, but now I´m totally lost.

Basic:

My source is a JSON file which I load via an API interface from a JIRA (confluence) instance in my company for issuetracking.

In this there are more than hundreds of different columns. So first step is to choose the needed columns in PBI, which are about 70 columns left.

Most of them I could already expand correctly, now I have 7 columns left which I need to expand so the result is as I would like to have it.

Those 7 columns are nested, so they have a list in there, expanded there are records. The list can have multiple record for one issue.

E.g. there is a column "Project team" for an issue with a list of persons -> list expanded with more records. Not all of the issues (rows in PBI) do have a project team, so most of the rows have null in there:

What I would like to have: in the column “Project team” (issue field 17807) a comma separated list of displayNames.

When I try to choose Extract Values – comma separated value I get the error message:

When I choose “Expand to new rows” I get multiple rows for each issue with only one value in the column project team. So its not that what I want.

I´ve added a custom column and entered the code there:

= Text.Combine(List.Transform([issues.fields.customfield_10600], each [displayName]), "; ")

Worked fine for another column (components, see below) but leads for this column also to the above error message.

As checking the solution here: https://social.technet.microsoft.com/Forums/en-US/6fc2c988-a93d-4938-962e-82f4a1676dd8/create-a-list-of-commaseparated-values-from-a-field-of-a-list-of-records?forum=powerquery this worked fine for my also for the component column.

The difference is, in the column components there are in all rows a list entry, although there are also empty entries. But then its just an empty list, and it has no NULL entry.

So I thought to replace somehow the null entries to solve the issue.

Replaced NULL with undefined, but then the column has no expand function anymore.

Added custom column with code: = Text.Combine(List.Transform([issues.fields.customfield_17807], each [displayName]), "; ")

leads as well to the same error:

I can´t remove rows where one of the 7 columns might be empty, because there are lots of other columns with valid and needed entries. I also don´t want to extract the multiple entries to single rows, because this might lead to several hundred rows for one single issue only because of the amount of entries in the nested columns.

So I guess I first need to transform somehow the rows with null entries to a type so that I can afterwards can extract the values as comma separated list in the column.

But I have now no idea anymore to do so and would be glad for any help.

Thanks and BR

Primlchen