Skip a record if empty

Question

I've created a function that cleans my data of extra columns with null values. There should always be 15 columns after this however occasionally there is more or less and when this happens those tables should just be removed.

I've tried just skipping all those rows and returning an empty table but when I try to expand those tables I get an error "Cannot convert the value false to type Number."

(tbl as table) =>
    let
        ColumnNames = Table.ColumnNames(tbl),
        RemoveNullColumns = Table.SelectColumns(tbl, List.Select(ColumnNames, each List.MatchesAny(Table.Column(tbl, _), each _ <> null))),
        CheckColumns = Table.Skip(RemoveNullColumns, Table.ColumnCount(RemoveNullColumns) <> 15)
    in
        CheckColumns

horseyride horseyride · Accepted Answer · 2019-10-30T19:49:37

See if this works for you. Removes any columns containing a null and returns tbl only if there are 15 remaining columns

(tbl as table) =>
let ColumnNames = Table.ColumnNames(tbl),
ReplacedValue = Table.ReplaceValue(tbl,null,"imanull",Replacer.ReplaceValue,ColumnNames ),
UnpivotedColumns = Table.UnpivotOtherColumns(ReplacedValue, {}, "Attribute", "Value"),
FilteredRows = Table.SelectRows(UnpivotedColumns, each ([Value] = "imanull")),
NonNullColumns= List.Difference(ColumnNames,List.Distinct(FilteredRows[Attribute])),
Results = if List.Count (NonNullColumns) <> 15 then null else Table.SelectColumns(tbl,NonNullColumns)
in Results

Skip a record if empty

1 Answers