How to handle failures from expanding empty lists?

Hello - here is one way you can handle this.

• Create a list of the field names returned when the list is not empty.  
  • (Option #1) below If you are wanting to integrate this prior to the #"Converted to Table" step, this list will be the field names of the list records returned when the list is not empty.  If it is placed here, some of the subsequent transformations may fail as they are attempting to operate on null values.
  • (Option #2) A better way would be to create a record with the final columns if the list is empty.  If the list is not empty, perform the transformations.  If you are combining the results of empty and non-empty outcomes, then append them.
• Get the list.
• Create an empty list handler which defines the return value in the event the list is empty.
• Define the list to be converted.  If empty, it is the list handler, otherwise it is the actual list.
• Convert to table for the list to be converted.
• Expand the table.

SCRIPT OPTION #1

let
    // This can be replaced with Record.FieldNames, etc. to generate the list dynamically.
    ColumnNames = {"CollectionName", "DatabaseName", "AutoscaleMaxThroughput", "db_collection_to_lower", "AccountName"},

    //------------------------------------------------------------------------------------------------
    // Sample List Values
    // Toggle between the result when the list is empty vs. when it is not empty by 
    // commenting one of the ListValues and uncommenting the other.
    //------------------------------------------------------------------------------------------------    

    // Sample value for an empty list.
    ListValue = {},

    // Sample value for a list that is not empty.
/*
    ListValue = {
        [
            CollectionName = "abc", 
            DatabaseName = "def", 
            AutoscaleMaxThroughput = "ghi", 
            db_collection_to_lower = "jkl",
            AccountName = "mno"
        ]
    },
*/
    // Define a list of null records with field names that match that of the populated records.
    EmptyListHandler = { Record.FromList ( List.Repeat ( { null }, List.Count ( ColumnNames ) ), ColumnNames ) },   
    // Define the list to be converted based on the contents.  If the ListValue is empty, then the Empty List Handler is returned.
    ListToConvert = if ListValue = {} then EmptyListHandler else ListValue,
    // Convert to table on the ListToConvert
    ListToTable = Table.FromList(ListToConvert, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    ListToTable