Your Power Query expression is happily skipping along, fetching data from a web API. Happily, that is, until one day its refreshes mysteriously die with the complaint that “column 'Column1' of the table wasn't found
“. You haven’t changed any M code. You’ve verified that no schema changes occurred on the external web service. How, then, could a column come up missing?
Or, maybe it’s not a missing column, but rather your fetch data code starts outputting an unexpected table row with a null value in each column. You manually try the web API using a tool like Postman or Insomnia and don’t find any all-null objects in the API’s response. Where is this all-null table row coming from?
Both of these unexpected occurrences potentially stem from the same underlying cause. As common M code patterns tend to not properly handle this situation, it is possible (even probable!) that M code you use may leave you susceptible to being bitten by one or the other of these bugs.
The Mysterious Missing Column
Why would M code that had been running fine start complaining about a column being missing if no schema changes have been made?
In Query Editor, if I use the New Source > From Web feature to connect to a JSON-returning web API, the generated code looks like:
let
Source = Json.Document(Web.Contents("https://example.com/some-url")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"data"}, {"data"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"data", type text}})
in
#"Changed Type"
Json.Document
(line 2) takes the data returned by Web.Contents
and converts it into a list of records, one per object contained in the JSON array returned by the API. Table.FromList
(line 3) is then used to turn this list into single-column table, one row per JSON-derived record. Next, Table.ExpandRecordColumn
(line 4) expands out the records in that single column, before a column type transformation is applied (line 5).
At least, this is what happens if the web API’s response array contains at least one JSON object—but what happens if it contains zero objects?
In this case, Table.FromList
does not output a single-column table. Instead, it returns a zero-column empty table. Why? Its input contained nothing to split into output columns, and since it was set to auto-define the columns it outputs, no columns were output.
When Table.ExpandRecordColumn
tries to take this table and expand its Column1, there’s no Column1 to be found, so dies complaining about the missing column.
The solution here is simple: Don’t rely on Table.FromList
to auto-define its output columns because it won’t output any columns if its input list is empty. Instead, use Table.FromList
‘s third argument to explicitly specify the column or columns to output (in this case, all that’s needed is Column1). If this is done, that function will always output the specified column(s), even when its input is empty. In turn, this will ensure that the expand record column step can always find the column it expects (Column1)—even when the JSON response contains no objects.
What’s needed is to make the following change:
Change:
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
To:
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Column1"}, null, ExtraValues.Error),
Moral of the Lesson
While the above solution fixes this specific bit of auto-generated M code, there’s also a bigger moral of the lesson that applies even if you are using different M logic:
Code that dynamically defines the columns it outputs by examining its input’s data items likely will cause an error if the input contains no items. Unless the input is guaranteed to never be empty, make your logic robust so that it gracefully handles empty input. Likely this means either:
- Explicitly defining output columns instead of relying on column auto-definition behavior.
- Providing a graceful bypass where your code checks for an empty data set (e.g. if
List.IsEmpty(input) then …
) and, if present, switches to alternate logic which doesn’t assume the presence of the missing columns.
Conclusion
We’ve explored a specific solution to problem scenario #1, and even derived a more generally applicable moral of the lesson from it. Example #2’s problem—the unexpected presence of an all-null row—also stems from zero rows being returned, but the specific reason why this occurs and its solution are different, and are something we’ll plan to look into soon.