I have made two table reports, one per sheet (tab). The third sheet tab is where I combine these two table because I have to get the information from two different areas.
Sheet 1: Table loads good
Sheet 2: Because no information was inserted into NAV yet, table returns the below result.
Sheet 3: I use the Data connections (under data tab in the ribbon) to combine the table tables using a query. This is then a stand alone table which is used for reports.
Here is where I run into a problem. Because I get the above result from sheet two. Sheet 3's table cannot refresh with the new data (i.e. no values from Sheet 2's table). Jet Reports therefore gives me the error:
My pivot table will not update because of the missing table information.
Is there a way to force Table Builder to display all headers regardless if it cant retrieve data out of NAV. I think if I can get that to work then it should be fine or do you think adding some sort of DAX expression into excel's query editor to not combine the two table if that table has no values?
Any feedback you can give me would be greatly appreciated and if you have any follow up questions for clarification please don't hesitate to say as much. I really need to find a solution to this problem. Thanks in advance :)