CODEWITHSUNDEEP
excelpowerbipowerquerym
Renier Wessels
Renier Wessels

Reputation: 151

Power Query Excel how to step over Expression.Error

I am getting an Expression.Error: There weren't enough elements in the enumeration to complete the operation.

The M code looks like this:

results = Sourcea[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
                      Record.FieldNames(#"Converted to Table"{0}[Column1]),
                      Record.FieldNames(#"Converted to Table"{0}[Column1])),

It all works perfectly until my results returns empty. The results come from a JSON API Get call. The error is on the Expanded Column1 step and it's because the columns are dynamically being expanded by the function so it works with any shape data set. Where the table is empty, the functions in the last line fails to enumerate.

Is there a way for me to avoid this error when outcomes are empty and just return "No results found" or similar in my Table / Output? Even just a blank table would suffice.

Upvotes: 1

Views: 613

Answers (1)

davidebacci
davidebacci

Reputation: 30304

You can count the rows of the table before expanding and only do so if there are > 0 rows. Something like:

#"Expanded Column1" = if Table.RowCount(#"Converted to Table") > 0 then 
                      Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
                      Record.FieldNames(#"Converted to Table"{0}[Column1]),
                      Record.FieldNames(#"Converted to Table"{0}[Column1])) 
                      else null,

Alternatively, you can wrap the statement in a try otherwise as described here: https://learn.microsoft.com/en-us/power-query/error-handling

Upvotes: 3

Related Questions