Deleting Blank Columns in Power Query

[khawarameer]

Hello All,

I need to import a 256 column table using power query [every fortnight through ODBC]. There are at least 150 columns in the table are empty however there is no fixed criteria that which column would be empty. I order to refine the data i need to delete these empty columns.

I used the Transpose option in transform menu to convert columns in to rows and used the delete blank rows option however it did not work as the column headers were appearing the every row after flip.

Now i need a formula which will count the number of items in each row and if that number is equal to 1 then that row is filtered. In excel i can do that using counta function but i am unable to do that in power query. Can any one help in this regard.

Khawar A. Malik

 

[Matt Allington]

How about unpivoting the columns - this will remove any blank columns. You can then re-pivot the data.

 

[khawarameer]

How about unpivoting the columns - this will remove any blank columns. You can then re-pivot the data.

Hello Matt,

I tried the suggested tip, first step worked fine i.e. un_pivot, however when i re_pivot the data instead of showing values it shows the count of values. I realize that most of the values in my original table are non numeric so re_pivoting will not work. Any other suggestion please?

Best regards

KAM.

 

[anvg]

Hi
Try

let
    source = Table.FromRows({
    {"name1", null,1,null},
    {"name2",null,null,null},
    {"name3",null,2,null}
    }),
    colNames = Table.ColumnNames(source),
    defNullCols = List.Transform(colNames,
    each if List.Count(List.RemoveNulls(Table.Column(source,_))) = 0 then _ else null
    ),
    nullNames = List.RemoveNulls(defNullCols),
    return = if List.Count(nullNames)>0 then Table.RemoveColumns(source,nullNames) else source
in
    return

Regards,

 

[khawarameer]

Hi
Try

let
    source = Table.FromRows({
    {"name1", null,1,null},
    {"name2",null,null,null},
    {"name3",null,2,null}
    }),
    colNames = Table.ColumnNames(source),
    defNullCols = List.Transform(colNames,
    each if List.Count(List.RemoveNulls(Table.Column(source,_))) = 0 then _ else null
    ),
    nullNames = List.RemoveNulls(defNullCols),
    return = if List.Count(nullNames)>0 then Table.RemoveColumns(source,nullNames) else source
in
    return

Regards,

Hello anvg,

I used your code with slight modification like this

let
    Source = Odbc.Query("DSN=IBG_PDM", "SELECT * FROM Table_Master.Account_Master(lf)WHERE Alt_Acct_NoLIKE '%HBFC1232%'"),
    colNames = Table.ColumnNames(source),
    defNullCols = List.Transform(colNames,
    each if List.Count(List.RemoveNulls(Table.Column(source,_))) = 0 then _ else null
    ),
    nullNames = List.RemoveNulls(defNullCols),
    return = if List.Count(nullNames)>0 then Table.RemoveColumns(source,nullNames) else Source
in
    Source

Code runs however column count still shows all columns further if i try to Load the query it gives following error

"[Expression Error] The name 'source' wasn't recognized. Make sure it's spelled correctly."

What could be the problem?

 

[khawarameer]

Hello anvg,

I have figured it out after adding correction. "You code worked fine" Thanks.

let
    Source = Odbc.Query("DSN=IBG_PDM", "SELECT * FROM Table_Master.Account_Master#(lf)WHERE Alt_Acct_NoLIKE '%HBFC1232%'"),
    colNames = Table.ColumnNames(Source),
    defNullCols = List.Transform(colNames,
    each if List.Count(List.RemoveNulls(Table.Column(Source,_))) = 0 then _ else null
    ),
    nullNames = List.RemoveNulls(defNullCols),
    return = if List.Count(nullNames)>0 then Table.RemoveColumns(source,nullNames) else Source
in
    return

Regards

Khawar A. Malik

 

[Useful]

Hello,

in your case it should work too

let
    Source = Odbc.Query("DSN=IBG_PDM", "SELECT * FROM Table_Master.Account_Master#(lf)WHERE Alt_Acct_NoLIKE '%HBFC1232%'"),
    unpcol = Table.UnpivotOtherColumns(Source, {}, "Attribute","Value"),
    collist = Table.Distinct(unpcol, {"Attribute"})[Attribute],
    final = Table.SelectColumns(Source,collist)
in
    final

 

[khawarameer]

Thanks My dear Useful,

You commands also worked fine.

Thanks again,

Regards

KAM