Pivot table from Data Model shows blank rows but none in source data

0
votes

I have a table (called MFInput1) that contains multiple rows and columns and NO blank rows. Now when I summarize this data directly as a pivot table - with the first column designated as row headings - I get the format I want, without any blank rows. However, when I try to create the pivot table by adding MFInput1 to a data model, I get a "(blank)" row at the bottom. As mentioned, there are no blanks in the source data (and the data model as well).

I need to include this table in the data model in power pivot as it has to link up with other tables to generate the final pivot report that I want and the blank row causes formula errors. I can filter out the blank row on the pivot table, but then every time the source table expands, I would have to re-filter the table to include the new entries.

To summarize: pivot table generated directly from source data table (that has no blanks) has no blank rows, but if generated from the same source data via a data model, a "(blank)" row shows up. How do I prevent this?

Any suggestions that might help would be highly appreciated. Thanks!

2
excelpowerpivot

2 Answers

0
votes

I have now figured out that it was relationships between the tables that was causing the "(blank)" row to show up.

For example, if the relationship between Table 1 and Table 2 is:

Column A {a, b, c, d} ---> Column B {a, b, c, d, e},

a "(blank)" row will show on a pivot generated from the data model if column A is used for row headers, as there is no correspondence to be found in column A for "e". In this case if blank rows are not desired, it would be necessary to delete the relationship between the tables (merely marking as "inactive" will not suffice apparently). But the following relationships work fine (will not generate "(blank)" rows).

Column A {a, b, c, d} ---> Column B {a, b, c, d}

Column A {a, b, c, d} ---> Column B {a, b, c,}

0
votes

I just had same issue due to source field contained STR and INT types in same field. Changed all to STR, data loaded successfully.