Hi
I have been asked to combine the data from the sheets in what I can only describe as the worst workbook ever. The file contains 20+ sheets. Each sheet currently has around 1600 columns of data (It is a daily file going back to 2017). There are around 300 rows in each sheet. The data is not in tables or named ranges. Before you say update the format and start again I can't this is not my file and the person who it belongs to will not entertain any changes!
So the issue I am having is two fold. Firstly the number of columns with actual data increases each week as new dates are added. Secondly there are some elements in some of the sheets that mean if you go end --> Home to go to the end of the used range it goes to a column that is around column 16,000 even though there is no actual data in that area. This means that when you try and combine the sheets it includes all of these empty columns in the data.
What would be the best approach to combining these sheets. Is there a way in the query that I can only select columns with actual text/numeric data in them? When new data is added how can I pick up the new columns?
I am off for a lie down as this file is the worst thing I have every seen 🙂
All help greatly received.
Bax
Hi,
You could create a function that removes all empty columns for you. A similar question came up several years ago in the Microsoft Tech Community.
The function looks like this:
(Source as table) as table =>
let
RemoveEmptyColumns = Table.SelectColumns(
Source,
List.Select(
Table.ColumnNames(Source),
each List.NonNullCount(Table.Column(Source, _)) <> 0
)
)
in
RemoveEmptyColumns
https://techcommunity.microsoft.com/t5/excel/power-query-remove-empty-columns/m-p/1331494
Tested it myself. It works though it may take a while when you are dealing with 16000 columns.
Riny
Thanks Riny. I will check this out.
