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

[vanessafvg]

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

[Nir1na]

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]

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]

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]

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

[vanessafvg]

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

[ImkeF]

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

[vanessafvg]

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

[ImkeF · 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

[PowerQPro]

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