Home

Deleted Columns Importing into PBI & Making Workbook too large

%3CLINGO-SUB%20id%3D%22lingo-sub-313612%22%20slang%3D%22en-US%22%3EDeleted%20Columns%20Importing%20into%20PBI%20%26amp%3B%20Making%20Workbook%20too%20large%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313612%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20normally%20use%20google%20sheets%20as%20my%20online%20xlsx%20editor%2C%20but%20recently%20moved%20over%20to%20Excel%20Online%20for%20the%20better%20PowerBI%20compatability.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20had%20a%20few%20issues%20with%20Excel%20Online%20however%20and%20hope%20that%20someone%20here%20can%20help.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20an%20attempt%20to%20mimic%20the%20layouts%20used%20on%20Sheets%2C%20I%20selected%20all%20of%20the%20columns%20to%20the%20right%20of%20the%20dataset%20and%20selected%20Delete.%20Well%2C%20that%20didn't%20work%2C%20and%20new%20Columns%20simply%20replaced%20it.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThose%20new%20columns%20are%20all%20blank%20on%20Excel%2C%20however%20when%26nbsp%3Bimporting%20into%20PBI%2C%20it%20brings%20along%20ALL%20of%20those%20deleted%20columns%20with%20null%20values.%20Thousands%20of%20them!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20searched%20online%20but%20cant%20seem%20to%20find%20a%20way%20to%20delete%20those%20null%20values.%20Does%20anyone%20here%20know%20what%20I%20need%20to%20do%20to%20get%20rid%20of%20them%3F%20If%20it%20helps%2C%20I%20have%20access%20to%20both%20Excel%20and%20Excel%20Online.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-313612%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314262%22%20slang%3D%22en-US%22%3ERe%3A%20Deleted%20Columns%20Importing%20into%20PBI%20%26amp%3B%20Making%20Workbook%20too%20large%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314262%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Stefan%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EScroll%20bar%20is%20an%20indicator%2C%20but%20it's%20more%20simple%20to%20press%20Ctrl%2BEnd%20at%20any%20place.%20It%20forwards%20you%20to%20very%20last%20cell%20in%20your%20workbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that%20cell%20is%20far%20after%20your%20data%20range%20ends%2C%20you%20may%20select%20all%20rows%20from%20end%20of%20that%20range%20to%20the%20row%20with%20last%20cell%20and%20Clear%20All%20that%20range.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20295px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F65937i8F7D96422DE7B24E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E(not%20sure%20what%20do%20you%20mean%20under%20Clear%20All%20Formatting).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20that%20Save%20your%20file%20and%20check%20Ctrl%2BEnd%20again%20-%20it%20shall%20at%20the%20end%20of%20your%20data%20range.%20Please%20note%2C%20if%20you%20don't%20save%20the%20file%20after%20Clear%20All%2C%20you%20see%20no%20changes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313928%22%20slang%3D%22en-US%22%3ERe%3A%20Deleted%20Columns%20Importing%20into%20PBI%20%26amp%3B%20Making%20Workbook%20too%20large%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313928%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20thanks%20for%20the%20response.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20not%20sure%20if%20I%20have%20the%20Pro%20version%20or%20not%2C%20its%20a%20business%20account%20-%20I%20assumed%20that%20was%20a%20different%20level%20unto%20its%20own.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20gave%20the%20%3CSTRONG%3EClear%20All%20Formatting%3C%2FSTRONG%3E%20option%20on%20the%20%3CSTRONG%3EEditing%3C%2FSTRONG%3E%20section%20of%20the%20%3CSTRONG%3EHome%3C%2FSTRONG%3E%20ribbon%20a%20try%2C%20by%20selecting%20the%20Columns%20%26amp%3B%20Rows%20in%20question%20first.%20It%20seemed%20to%20help%20with%20columns%2C%20but%20not%20rows%20and%20heres%20why%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EI%20have%202%20tabs%20on%20my%20workbook.%20One%20tab%20has%20around%20300%20rows%20of%20data%20and%20the%20other%20has%20around%20900.%3C%2FLI%3E%3CLI%3EWhen%20i%20scroll%20down%20half%20way%20through%20each%2C%20the%20vertical%20scroll%20bar%20is%20radically%20different%20on%20the%20two%20of%20them.%20On%20the%20900%2C%20its%20not%20moved%20at%20all%2C%20where%20as%20the%20300%20is%20half%20way%20to%20the%20bottom.%3C%2FLI%3E%3CLI%3ELikewise%2C%20when%20I%20navigate%20to%20the%20bottom%20of%20the%20entered%20data%20for%20each%20tab%2C%20the%20300%20tabs%20vertical%20scroll%20bar%20is%20pretty%20much%20at%20the%20bottom.%20Sure%20I%20can%20continue%20scrolling%20down%20and%20the%20vertical%20scroll%20bar%20shrinks%20to%20accommodate.%20But%20when%20I%20go%20back%20to%20the%20top%2C%20the%20scroll%20bar%20gets%20bigger%20again%2C%20as%20though%20we%20never%20scrolled%20down.%26nbsp%3B%3C%2FLI%3E%3CLI%3EHowever%2C%20on%20the%20900%20row%20tab%2C%20when%20looking%20at%20the%20final%20few%20rows%20of%20data%2C%20the%20vertical%20scroll%20bar%20is%20still%20pretty%20much%20at%20the%20top%20of%20the%20screen.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThis%20has%20me%20thinking%20that%2C%20for%20some%20reason%2C%20Excel%20is%20still%20looking%20for%20all%20100%2C000%20rows%20on%20that%20tab%2C%20instead%20of%20simply%20the%20900%20or%20so%20that%20I%20am%20using.%20Is%20this%20is%20a%20known%20issue%2C%20or%20something%20I%20can%20work%20around%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313734%22%20slang%3D%22en-US%22%3ERe%3A%20Deleted%20Columns%20Importing%20into%20PBI%20%26amp%3B%20Making%20Workbook%20too%20large%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313734%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Stefan%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%26nbsp%3Bhave%20Pro%20version%26nbsp%3Bit%20shall%20be%20Inquire%20from%20which%20you%20may%20Clean%20Excess%20Cell%20Formatting%3C%2FP%3E%3C%2FLINGO-BODY%3E
Stefan_M
New Contributor

Hi everyone, 

 

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. 

3 Replies
Highlighted

Hi Stefan,

 

If you have Pro version it shall be Inquire from which you may Clean Excess Cell Formatting

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;

 

  • I have 2 tabs on my workbook. One tab has around 300 rows of data and the other has around 900.
  • When i scroll down half way through each, the vertical scroll bar is radically different on the two of them. On the 900, its not moved at all, where as the 300 is half way to the bottom.
  • Likewise, when I navigate to the bottom of the entered data for each tab, the 300 tabs vertical scroll bar is pretty much at the bottom. Sure I can continue scrolling down and the vertical scroll bar shrinks to accommodate. But when I go back to the top, the scroll bar gets bigger again, as though we never scrolled down. 
  • However, on the 900 row tab, when looking at the final few rows of data, the vertical scroll bar is still pretty much at the top of the screen.

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?

Hi Stefan,

 

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.

image.png

(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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies