Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
vanessafvg
Super User
Super User

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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




1 ACCEPTED SOLUTION

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

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

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

View solution in original post

9 REPLIES 9
Nir1na
New Member

Transpose, Remove Empty Rows, Transpose

 

in the advanced Editor, you'd have:

#"Transposed Table" = Table.Transpose(Last step),
#"Removed Blank Rows" = Table.SelectRows(#"Transposed Table", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Transposed Table1" = Table.Transpose(#"Removed Blank Rows")

PowerQPro
Frequent Visitor

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

 

shebr
Resolver III
Resolver III

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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

 

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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.