cancel
Showing results for 
Search instead for 
Did you mean: 
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 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.