Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

power pivot

ResolvedVersion 2016

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!

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

Excel tip:

Create a unique items table from a duplicating table

1. Ensure that your list has column headings
2. Select the entire list
3. From the menu bar, select DATA, FILTER, ADVANCED FILTER
4. Select "Filter the list, in place", and tick the "Unique Records Only" box
5. Click OK, filtered list appears.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.15 secs.