Power Pivot

Resolved · High Priority · Version 2016

Fri 20 Oct 2017, 18:05 Delegate Emma said...

Emma has attended:
Excel PowerPivot course

Power Pivot

I am importing tables into Power Pivot using the import function - the tables do not have any blank rows at the bottom. When I then view them in PowerPivot blank rows have been added at the bottom - this means that I am unable to create relationships between tables as the repeated blank rows at the bottom mean that the relationships are many to many.

I have checked that this is the only duplicate, I am unable to delete the rows. Filtering the information to excluded nil rows does not solve the problem either.

Currently unable to progress at all!

Mon 23 Oct 2017, 11:47 Trainer Jens said...

RE: Power Pivot

Hi Emma,

Thank you for the forum question.

Sorry but I am almost sure that you have blank rows in your source data. In the Excel worksheet where you have the source data please select A1 and press Ctrl End. Excel will now select the last cell in the worksheet with content. If this cell is, lets say in row 200 and your visible source data end in row 100 you will need to select all rows from row 101 to 200 and click Clear All from the home tab in the Editing group and save the file.

The problem is if you have formatted rows under you visible source, Excel will import this to your data model.

If this is not the issue then you have another option:

In the the import wizard you get an option to Review & Filter (bottom right corner) on the step where you select the worksheet you want to import. Click this option and click on one of the filter next to your heading. Just untick the Blank option and you will not import the blank rows.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Recovering Unsaved Work in Excel 2010

Ever closed your Excel workbook in a hurry without saving your work beforehand? Here's how to get it back.

Choose File then Info, click Manage Versions and then Recover Unsaved Workbooks which will automatically find the spreadsheets that haven't been saved.

View all Excel hints and tips