Forum Discussion
JSON: Expand nested columns with also NULL entries leads always to error
Super User
See if you can use the solution below:
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
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 null3. 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
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] <> "")