Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
I want to remove all empty columns, what is the quickest way to do this?
Proud to be a Super User!
Solved! Go to 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
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")
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
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
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.
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?
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
126 | |
110 | |
87 | |
70 | |
66 |