I had an interesting problem recently when the JSON output from an API started to return a null value instead of empty list. This broke my PowerQuery so I wanted to share a fix.

Originally, I used the built-in “Extract values (from a list)” feature, which concatenated them to make comma separated values:

= Table.TransformColumns(#"Removed Columns", {"List_Field", each Text.Combine(List.Transform(_, Text.From), ","), type text})

And that worked fine. Until the content of that field was not a list. Evidently, this field had previously contained an empty JSON list if it contained no values. I’ve never looked at the JSON itself so I had no idea. So, when the API started returning null for that field instead List.Transform failed:

Expression.Error: We cannot convert the value null to type List.

In some of my other Queries it was also failing “silently” because this step was immediately followed by a RemoveRowsWithErrors step. Whoops. Having no idea that the API output had changed (or what it originally contained) was my big problem here. Took me a while to understand what had gone wrong and how.

The fix itself isn’t complex, simply check if the value is a list before concatenation:

= Table.TransformColumns(#"Removed Columns", {"List_Field", each if Value.Is(_, type list) then Text.Combine(List.Transform(_, Text.From), ",") else null, type text})

However, that DOES make me wonder why the built-in doesn’t automatically add code to check the type. Can’t be that hard…