Forum Discussion

igaca's avatar
igaca
Helper III
10 years ago
Solved

Power Query - removing all columns with no values?

Hi,   Am wondering if someone has worked out a way to remove all columns containing no values (as in null / nothing...no zeroes) without checking each column in a query from its drop-down to check?...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    10 years ago

    Hi igaca,

     

    Based on my understanding, you want to remove the null value column with power query, right?

    If as I said, you could follow below steps:

     

    1. Create a test table, and load it to power bi, click 'edit query'.

     

     

    2. Select all of the table and click the ‘Unpivoted Columns’.

     

     

     3. Choose the first Column and click ‘Removed duplicates’, then modify the query to remove the second column.

    Modify:

    Table.Distinct(#"Unpivoted Columns", {"Attribute"})  =>  Table.Distinct(#"Unpivoted Columns", {"Attribute"})  [Attribute]

     

    4. Click on ‘fx’ to add a custom step:

     

    Use Table.SelectCoulmns function to get the specify columns:

     

     

    For more detail info, you could refer to below link:

    Video_017 How to Remove Null Columns with Power Query?

     

    Regards,

    Xiaoxin Sheng

ImkeF's avatar
ImkeF
Community Champion
10 years ago

Hi Igor,

a quick & dirty-way is to unpivot your columns and then pivot-back. This will remove all columns with null only. But this can get slow for big tables.

In that case you can use this function instead:

 

(table) =>
 Table.SelectColumns(table, List.Select(Table.ColumnNames(table), each List.NonNullCount(Table.ToColumns(Table.SelectColumns(table, _)){0})>0))
  • mightycrown's avatar
    mightycrown
    Frequent Visitor
    9 years ago

    HI Imkef,


    I d like to use this function. However it doesn't work

    This is my current query

    let
Source = Oracle.Database("ORACLSQL", [HierarchicalNavigation=true]),
PO = Source{[Schema="PO"]}[Data],
PO_REQUISITION_HEADERS_ALL1 = PO{[Name="PO_REQUISITION_HEADERS_ALL"]}[Data],
Custom1 = FnRemoveEmptyColumns
in
Custom1

    I put the function to FnRemoveEmptyColumns

     

    Can you help

    • ImkeF's avatar
      ImkeF
      Community Champion
      9 years ago

      Pls try this:

       

      let
Source = Oracle.Database("ORACLSQL", [HierarchicalNavigation=true]),
PO = Source{[Schema="PO"]}[Data],
PO_REQUISITION_HEADERS_ALL1 = PO{[Name="PO_REQUISITION_HEADERS_ALL"]}[Data],
Custom1 = FnRemoveEmptyColumns(PO_REQUISITION_HEADERS_ALL1)
in
Custom1

      ... you need to pass an argument to the function

       

      • mightycrown's avatar
        mightycrown
        Frequent Visitor
        9 years ago
        ImkeF wrote:

        Pls try this:

         

        let
Source = Oracle.Database("ORACLSQL", [HierarchicalNavigation=true]),
PO = Source{[Schema="PO"]}[Data],
PO_REQUISITION_HEADERS_ALL1 = PO{[Name="PO_REQUISITION_HEADERS_ALL"]}[Data],
Custom1 = FnRemoveEmptyColumns(PO_REQUISITION_HEADERS_ALL1)
in
Custom1

        ... you need to pass an argument to the function

         

        yep. thanks for the help. 

         

         

        Any trick to exclude headers? 

         

        Right now all the columns have a header, but I want eliminate these columns with no data in it.

  • Rockstar's avatar
    Rockstar
    New Member
    7 years ago

    Thanks for this one! For those of us that are already working with unpivoted and repivoted data, this works quite nicely