Power Query - removing all columns with no values?

[igaca]

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?  I would ideally like to apply this as a step.

Thanks ahead for any constructive input!

Igor

[Anonymous]

There's a much easier solution to this.

On the Home tab, select Choose Columns available under Manage Columns,

And filter out the null value columns

[Anonymous · Accepted solution]

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

[Anonymous]

If anyone is interested:
i solved the problem by using the editor and following steps:

#"Removed Top Rows" = Table.Skip(#"Changed Type",4),
    #"Removed Empty Columns" = Table.SelectColumns(#"Removed Top Rows", List.Select(Table.ColumnNames(#"Removed Top Rows"), each List.NonNullCount(Table.Column(#"Removed Top Rows", _)) > 0))
in
    #"Removed Empty Columns"

[ImkeF]

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))

[Rockstar]

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

[mightycrown]

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]

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]

---

@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.

[ImkeF]

Not sure if I understood you, but maybe this is what you'e looking for:

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(Table.PromoteHeaders(PO_REQUISITION_HEADERS_ALL1))
in
Custom1

[Tow]

Ok, during the query is fine.

But what, if I want them to be removed, only if the context of the user filtering reveals, that the columns got just null rows?

Is there an idea, how to handle that?

[a_mixed_life]

Are you wanting to filter it out during the query?

With my query when I didn't want 'null' or blank to be pulled from a specific column, I did this formula:

([Posted Source ID] <> null and [Posted Source ID] "")

*Posted Source ID was the column I was wanting to filter out

Hope that's what you're looking for.