Remove blank columns from all tables before combining the tables

[Here4Help]

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. 

[slorin]

Hi 

= 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

[Akash_Varuna]

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 them

[Here4Help]

Thanks. I tried it and I am getting this error. 

[Akash_Varuna · Accepted solution]

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))
)
)}
)

[Here4Help]

Thanks a lot. That worked in removing the blank columns. However, now all the sheets have different column names (although data is in correct order). And when I use Table.combine to combine the sheets, it is combining by column names.

Is there a way to ignore the column names while using Table.combine?

Thanks

[Akash_Varuna]

Hi @Here4Help , Try These please

Use Table.ToRows to convert each table into a list of rows, ignoring column names.

Use List.Combine to merge all rows into a single list.

Convert the combined list back into a table with Table.FromRows.

Rename columns using Table.RenameColumns to assign consistent names.
If the post helped please do give a kudos and accept the above as a solution

[Here4Help]

Thanks, I demoted the headers and then combined the files. Thanks a lot for your help.

[Vijay_A_Verma]

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"})