Forum Discussion

Patryk_PL_92's avatar
Patryk_PL_92
Helper I
4 years ago
Solved

Power Query - remove columns based on condition

Hi ,

 

I am looking for solution to remove empty columns.

By empty I mean not 100% empty, but condition should be based on "% of empty cells in column" - like: 

 

remove columns with 95% empty/blank cells

or

remove columns which have only 10 or less cells with some value.

 

Is it possible to achieve that?
Which function to use?

 

As for now, I was only able to remove 100% empty cells by using combination of:
- remove blank rows
- transpose

- remove blank rows

- transpose

But still after that I have columns which have for example only 1 or 2 cells with value which I do not need and the rest (few thousands rows) are empty. There are about 100 columns in total and after cleaning, there should be 20-30 columns with proper data.

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    Below I have used 1-0.95 which means if count is less than 95% then remove it. You can adjust it accordingly 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYs3DsAwDAP/otlLetb03rMZ/v83YkcQYHoQwONRWlNFiiJ7iT0ySlPtgqJUuGHO/s4VLRfiO865cI96QBwRJ86F8MxcCi84XwO92RB7fsf5gXgG3xfqG/EJ1q+nzQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    RowCount = Table.RowCount(#"Changed Type"),
    ColumnsListforRemoval = List.Select(Table.ColumnNames(#"Changed Type"),(x)=>(List.NonNullCount(Table.Column(#"Changed Type",x))/RowCount)<(1-0.9)),
    RemoveColumns = Table.RemoveColumns(#"Changed Type",ColumnsListforRemoval)
in
    RemoveColumns

     if you want to make it on the basis of number of non blank cells say remove if a column has less than 10 non blank, then change this with line

    ColumnsListforRemoval = List.Select(Table.ColumnNames(#"Changed Type"),(x)=>List.NonNullCount(Table.Column(#"Changed Type",x))<10)

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    Below I have used 1-0.95 which means if count is less than 95% then remove it. You can adjust it accordingly 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYs3DsAwDAP/otlLetb03rMZ/v83YkcQYHoQwONRWlNFiiJ7iT0ySlPtgqJUuGHO/s4VLRfiO865cI96QBwRJ86F8MxcCi84XwO92RB7fsf5gXgG3xfqG/EJ1q+nzQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    RowCount = Table.RowCount(#"Changed Type"),
    ColumnsListforRemoval = List.Select(Table.ColumnNames(#"Changed Type"),(x)=>(List.NonNullCount(Table.Column(#"Changed Type",x))/RowCount)<(1-0.9)),
    RemoveColumns = Table.RemoveColumns(#"Changed Type",ColumnsListforRemoval)
in
    RemoveColumns

     if you want to make it on the basis of number of non blank cells say remove if a column has less than 10 non blank, then change this with line

    ColumnsListforRemoval = List.Select(Table.ColumnNames(#"Changed Type"),(x)=>List.NonNullCount(Table.Column(#"Changed Type",x))<10)
  • Patryk_PL_92's avatar
    Patryk_PL_92
    Helper I
    4 years ago

    Hi Vijay,

     

    thanks for your effort, it seems that this is what I was looking for 🙂