Blank rows showing in table when reference given to table in another Excel file

[harrylippy]

Hi everyone!

Here's what I'm trying to do, and my problem (with screenshots). I would like to have a master spreadsheet that I use as my underling database for a PowerBI report. Everything is working great for the most part, but I have one problem. I have dynamically sized tables in the source spreadsheet that I am linking to from my master sheet. Here is my source table:

Now, in a separate Excel file, I am linking to this table. When I say "linking to this table", I mean I am creating a table in the new file and, for each of the columns, entering "= " and then going to the other file and selecting the corresponding column and hitting Enter. The image below shows the result, and I have encirled in red where I believe my problem originates (which I'll describe below):

Here is my PowerBI table visual, and my problem is encircled in red (note that I have ensured that "show items with no data" is NOT selected:

As you can see, PowerBI is - and this is a guess - interpreting the link as meaning that the cells are NOT empty, and is displaying the contents of the cells. What I would like is for cells with no data in the original source table to be reflected as cells with no data in the table that I am using in my master datasheet, which has references to the empty cells in the source file/table. I hope I'm expressing that clearly, and hopefully the screenshots help.

Here's why I'm doing this the way that I am - if there is a better way that will help me circumvent this issue, I'm happy to hear about it. The table in my source document will be dynamically sized - sometimes rows will be added, sometimes rows will be deleted. I need that to be reflected automatically in the destination table in my master datasheet. The only way I could figure to do this was to make the table large enough so that I know there will always be enough rows for the data, and then have the destination table just refer to all of the rows, including the empty ones. It seems to work great in the Excel view, but the visuals are tripping up on it, as you can see above. If there is a way to create a reference to a table, rather than references to the cells in a table, maybe I don't have to do the workaround of entering a lot of rows, many (most) of which will be empty most of the time. Happy to hear thoughts on that.

Thanks in advance for any help, PowerBI community!

Shaun Lippy

[lbendlin]

Note that Power BI allows you to distinguish between Excel sheets and Excel tables as data sources. Define your tables as needed in Excel and you get the contents automatically. Or choose to connect to the sheet and filter the unneeded rows in Power Query.