Forum Discussion
Remove blank columns from all tables before combining the tables
Hi,
So, I have these tables (around 100) which I want to combine. But I want to remove all empty columns in each of these tables before I combine them.
Any help is really appreciated. Thanks.
To give a bit of context, I am importing data from excel files where each file has multiple sheets. These sheets are all heavily formatted and have multiple empty columns which don't allign. I need to combine these sheets after remvoing the empty columns so that the data from all the empty sheets align. I cannot use the column names to do it because that's in the middle of the page etc.
It might be beacuse of the data type or the previous step you are refrencing try this please
Table.TransformColumns(
#"previous step",
{"Custom", each Table.RemoveColumns(
_,
List.Select(
Table.ColumnNames(_),
(x) => List.AllTrue(List.Transform(Table.Column(_, x), (y) => y = "" or y = null))
)
)}
)
8 Replies
- Vijay_A_Verma
Most Valuable Professional
You can inset this step. Replace #"previous step" with your previous step name.
= Table.ReplaceValue(#"previous step", each [Custom], each Table.RemoveColumns([Custom], List.Select(Table.ColumnNames([Custom]), (x)=> List.AllTrue(List.Transform(Table.Column([Custom], x), (y)=> y ="" or y =null)))), Replacer.ReplaceValue, {"Custom"}) - Akash_Varuna
Super User
Hi first
Connect to your Excel files in Power Query and load the "Table" objects.
Remove blank columns: Add a custom column with this formula to filter out empty columns dynamically
Table.SelectColumns([Custom], List.Select(Table.ColumnNames([Custom]), each List.NonNullCount(Table.Column([Custom], _)) > 0))
Then Expand themThanks. I tried it and I am getting this error.
- Akash_Varuna
It might be beacuse of the data type or the previous step you are refrencing try this please
Table.TransformColumns(
#"previous step",
{"Custom", each Table.RemoveColumns(
_,
List.Select(
Table.ColumnNames(_),
(x) => List.AllTrue(List.Transform(Table.Column(_, x), (y) => y = "" or y = null))
)
)}
)
slorinHi
= Table.TransformColumns(
Your_Source,
{{"Your_Column",
each Table.SelectColumns(
_,
Table.SelectRows(
Table.AddColumn(Table.Profile(_), "X", each [Count]<>[NullCount]),
each [X])[Column]),
type table}})Stéphane
