Forum Discussion
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
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
11 Replies
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))HI Imkef,
I d like to use this function. However it doesn't workThis 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 Custom1I put the function to FnRemoveEmptyColumns
Can you help
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
Thanks for this one! For those of us that are already working with unpivoted and repivoted data, this works quite nicely
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
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"
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.
