cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

 

Thanks ahead for any constructive input!

 

Igor

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Power Query - removing all columns with no values?

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

 Capture.PNG

 

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

 Capture2.PNG

 

Capture3.PNG

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

Capture4.PNG

Modify:

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

Capture5.PNG

 

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

 Capture6.PNG

Use Table.SelectCoulmns function to get the specify columns:

 Capture7.PNG

 

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

Video_017 How to Remove Null Columns with Power Query?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Highlighted
Resolver I
Resolver I

Re: Power Query - removing all columns with no values?

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.

Kris
Highlighted
Super User III
Super User III

Re: Power Query - removing all columns with no values?

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Community Support
Community Support

Re: Power Query - removing all columns with no values?

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

 Capture.PNG

 

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

 Capture2.PNG

 

Capture3.PNG

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

Capture4.PNG

Modify:

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

Capture5.PNG

 

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

 Capture6.PNG

Use Table.SelectCoulmns function to get the specify columns:

 Capture7.PNG

 

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

Video_017 How to Remove Null Columns with Power Query?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Power Query - removing all columns with no values?

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

Highlighted
Super User III
Super User III

Re: Power Query - removing all columns with no values?

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Frequent Visitor

Re: Power Query - removing all columns with no values?


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

Highlighted
Super User III
Super User III

Re: Power Query - removing all columns with no values?

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Frequent Visitor

Re: Power Query - removing all columns with no values?

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?

Highlighted
New Member

Re: Power Query - removing all columns with no values?

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors