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

[primlchen]

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...

and also https://community.powerbi.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=fals...

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... 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

[primlchen]

Hi @v-henryk-mstf,

already checked all of those solutions you have posted, none of them are helpful for my case.

The error messages are all posted above. The problem is, I have columns with NULL and LIST entries in there, so none of the syntax from the mentioned posts work here. They only work in columns which have all an LIST (or table) entry, not for mixed with NULL values.

So I suppose I need to either transform the NULL to empty list or to somehow exclude them in the syntax e.g. = Text.Combine(List.Transform([issues.fields.customfield_10600], each [displayName]), "; ")

BR Marion

[DataInsights]

@primlchen,

See if you can use the solution below:

https://community.powerbi.com/t5/Desktop/How-to-expand-a-list-to-individual-columns-and-not-rows/m-p... 

It uses a custom column in Power Query that allows you to separate the table into two tables: one with lists, and one without lists. This enables you to apply specific logic to each table, and then append the tables once complete.

if Value.Is([Project.customFields], type list) 
then 1
else 0

[primlchen]

Hi @DataInsights

thanks for the input, I´ll try this and let you know if it works.

BR primlchen

[primlchen]

Hi @DataInsights @v-henryk-mstf 

I found now the solution. 

1. copy the existing query and removed all columns except Key and desired column.

2. Add a custom column in the new query and add code:

if Value.Is([issues.fields.customfield_20206], type list) 
then Text.Combine(List.Transform([issues.fields.customfield_20206], each [value]), "; ")
else null

3. Remove emtpy from that column.

Result is that what I want to have, coma separated values in one column: 

One last point I´m thinking about how to maybe add the step of removing the empty entries to the else-condition in the coding above.

Try to use the code by coping from the advanced editor after the last step, but this leads to an error.

Added to if-else condition like this

if Value.Is([issues.fields.customfield_20206], type list)

then Text.Combine(List.Transform([issues.fields.customfield_20206], each [value]), "; ")

else Table.SelectRows(#"Added Custom1", each [issues.fields.customfield_20206] <> null and [issues.fields.customfield_20206] <> "")

Error:

But this is a nice to have, in generall it works now. Thanks for your input.

BR primlchen

[DataInsights]

@primlchen,

Have you tried adding this step before the custom column Master Data Relevant?

Table.SelectRows(#"Added Custom1", each [issues.fields.customfield_20206] <> null and [issues.fields.customfield_20206] <> "")

[v-henryk-mstf]

Hi @primlchen ,

Check if the following similar solutions are helpful to you.

Solved: Re: Expression.Error: We cannot convert the value ... - Microsoft Power BI Community

Re: "cannot convert the value null to type list" a... - Power Platform Community (microsoft.com)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.