01-11-2019 04:33 AM
01-11-2019 04:33 AM
I normally use google sheets as my online xlsx editor, but recently moved over to Excel Online for the better PowerBI compatability.
I have had a few issues with Excel Online however and hope that someone here can help.
In an attempt to mimic the layouts used on Sheets, I selected all of the columns to the right of the dataset and selected Delete. Well, that didn't work, and new Columns simply replaced it.
Those new columns are all blank on Excel, however when importing into PBI, it brings along ALL of those deleted columns with null values. Thousands of them!
I have searched online but cant seem to find a way to delete those null values. Does anyone here know what I need to do to get rid of them? If it helps, I have access to both Excel and Excel Online.
01-11-2019 07:36 AM
If you have Pro version it shall be Inquire from which you may Clean Excess Cell Formatting
01-11-2019 12:55 PM - edited 01-11-2019 12:57 PM
Hi @Sergei Baklan thanks for the response.
I am not sure if I have the Pro version or not, its a business account - I assumed that was a different level unto its own.
I gave the Clear All Formatting option on the Editing section of the Home ribbon a try, by selecting the Columns & Rows in question first. It seemed to help with columns, but not rows and heres why;
This has me thinking that, for some reason, Excel is still looking for all 100,000 rows on that tab, instead of simply the 900 or so that I am using. Is this is a known issue, or something I can work around?
01-13-2019 06:32 AM
Scroll bar is an indicator, but it's more simple to press Ctrl+End at any place. It forwards you to very last cell in your workbook.
If that cell is far after your data range ends, you may select all rows from end of that range to the row with last cell and Clear All that range.
(not sure what do you mean under Clear All Formatting).
After that Save your file and check Ctrl+End again - it shall at the end of your data range. Please note, if you don't save the file after Clear All, you see no changes.