Power query : set a delete condition such as when column empty cell percentage exceeds 90%

[yxz152830]

Gurus,
I have a table from external source. I know for sure that if a column has only 1 or 2 cells with strings, the column is for remarks or something else. I want to remove them using powery query not vba but I don't know how to set up the criteria. Is my title criterion viable in power query? Or should I just load the table and clean it with vba? Thank you!

 

[JGordon11]

This query will transform an excel table named Table1 by deleting all columns where 90% or more of that column's values are null.

Not sure whether that is exactly what you're looking for.

let
    /* UDF */ Countif = (lst as list, value as any) => List.Accumulate(lst,0, (s,c)=> s+ (if c = value then 1 else 0)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    lst = Table.ToColumns(Source),
    tcn = Table.ColumnNames(Source),
    lc = List.Count(lst),
    rc = Table.RowCount(Source),
    threshold = 0.90,
    KeepColumn = List.Generate(()=>0,(x)=> x< lc, (x)=> x+1, (x)=> if Countif(lst{x}, null)/rc < threshold then true else false),
    Cols = List.RemoveNulls(List.Generate(()=>0,(x)=> x< lc, (x)=> x+1, (x)=> if KeepColumn{x} then [Columns = lst{x}, ColNames = tcn{x}] else null)),
    tbl1 = Table.FromList(Cols,Record.FieldValues, {"Columns","ColNames"}),
    Result = Table.FromColumns(tbl1[Columns],tbl1[ColNames])
in
    Result

 

[JGordon11]

Here's a better way:

[CODE=pq]
let
    /* UDF */ Countif = (lst as list, value as any) => List.Accumulate(lst,0, (s,c)=> s+ (if c = value then 1 else 0)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    lst = Table.ToColumns(Source),
    tcn = Table.ColumnNames(Source),
    lc = List.Count(lst),
    rc = Table.RowCount(Source),
    threshold = 0.90,
    KeepColumn = List.RemoveNulls(List.Generate(()=>0,(x)=> x< lc, (x)=> x+1, (x)=> if Countif(lst{x}, null)/rc < threshold then tcn{x} else null)),
    Result = Table.SelectColumns(Source, KeepColumn)
in
    Result

[/CODE]