cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vanessafvg Super Contributor
Super Contributor

whats the quickest way to remove empty columns in query editor / m

I want to remove all empty columns, what is the quickest way to do this?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
1 ACCEPTED SOLUTION

Accepted Solutions
ImkeF
MVP

Re: whats the quickest way to remove empty columns in query editor / m

It's probably easier to paste the function code as a separate query like described here:

https://www.youtube.com/watch?v=6TQN6KPG74Q

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

8 REPLIES 8
shebr Member
Member

Re: whats the quickest way to remove empty columns in query editor / m

Hi @vanessafvg

 

Do you mean other than right clicking the header and select remove? Or Highlight the ones you want to keep and select 'Remove Other columns. Or, you can select the 'Choose columns' button on the ribbon and select the ones you want to keep.

 

Thanks

 

shebr

vanessafvg Super Contributor
Super Contributor

Re: whats the quickest way to remove empty columns in query editor / m

hi @shebr i want to programmatically via M remove them all without having to click on each one.


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
vanessafvg Super Contributor
Super Contributor

Re: whats the quickest way to remove empty columns in query editor / m

@ImkeF or @Vvelarde  any ideas on how to do this quickly in one m statement?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
ImkeF
MVP

Re: whats the quickest way to remove empty columns in query editor / m

You can use this function:

 

(tbl as table) =>
let
    Headers = Table.ColumnNames(tbl),

    Result = Table.SelectColumns(
                 tbl,
                 List.Select(Headers, each List.MatchesAny(Table.Column(tbl, _), each _ <> null)))
in
    Result

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




vanessafvg Super Contributor
Super Contributor

Re: whats the quickest way to remove empty columns in query editor / m

@ImkeF thats great thank you, one last question

 

if i wanted to add this into this sample file transformation as a result from combinining binaries, i can't quite figure out how to combine the code with the function, i have tried but im obviously not quite understanding what needs to be done

this is the mcode

let
Source = Excel.Workbook(#"Sample File Parameter7", null, true),
#"Service Report_Sheet" = Source{[Item="Service Report",Kind="Sheet"]}[Data]
in
#"Service Report_Sheet"

 

 

this is what i tried

 

 

let
Source = Excel.Workbook(#"Sample File Parameter7", null, true),
#"Service Report_Sheet" = Source{[Item="Service Report",Kind="Sheet"]}[Data],
tbl = table,
Headers = Table.ColumnNames(tbl),
#"Result" = Table.SelectColumns(
tbl,
List.Select(Headers, each List.MatchesAny(Table.Column(tbl, _), each _ <> null)))

in
#"Result"

 

i get this error Expression.Error: The import table matches no exports. Did you miss a module reference?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
ImkeF
MVP

Re: whats the quickest way to remove empty columns in query editor / m

It's probably easier to paste the function code as a separate query like described here:

https://www.youtube.com/watch?v=6TQN6KPG74Q

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

PowerQPro Frequent Visitor
Frequent Visitor

Re: whats the quickest way to remove empty columns in query editor / m

It seems like this taking significant time, any advice to reduce time it takes?

PowerQPro Frequent Visitor
Frequent Visitor

Re: whats the quickest way to remove empty columns in query editor / m

Best Soluthion I found if you have a smaller size area to collect data.

 

https://www.mrexcel.com/forum/excel-questions/924666-deleting-blank-columns-power-query.html

 

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Reorganized = Table.FromColumns({Table.ColumnNames(Source),Table.ToColumns(Source)}),
    #"Added Custom" = Table.AddColumn(Reorganized, "IsNull", each if List.NonNullCount(List.Distinct([Column2]))=0 then "Yes" else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([IsNull] = "No")),
    #"Clean Table" = Table.SelectColumns(Source,Table.Column(#"Filtered Rows","Column1"))
in
    #"Clean Table"

You just have to replace the source with the table on which you want to apply the operation

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,720)