Forum Discussion

primlchen's avatar
primlchen
Frequent Visitor
3 years ago

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...
Need Help
primlchen's avatar
primlchen
Frequent Visitor
3 years ago

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's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    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/2713111#M947992 

     

    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's avatar
      primlchen
      Frequent Visitor
      3 years ago

      Hi DataInsights

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

      BR primlchen

       

      • primlchen's avatar
        primlchen
        Frequent Visitor
        3 years ago

        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