>>I have 3 different data files(.xlsx) I am using table to show data.
How are you loading them? What kind of table?
>>Requirement is to show that table as a template.
Not sure what this means. Please provide a little more detail.
>>whenever is data is missing from any particular file then that row disappears
What would you like to see when the data is missing?
Thanks for your response and apologies for unclear requirement
1. Loading .xlsx using simple/Normal load.
2. Created a Pivot table.
3. expected to receive 5 rows from each file. so there should be 15 rows in pivot table
4. Now as per the requirement even if I receive 3 rows from each file, pivot table must display 15 rows always. and 0 against those 6 rows (which are not received)
Hope the requirement is clear now.
Well its tricky to display missing data.Missing is not the same as null, and no expression will be evaluated. Can you even load completely empty rows from Excel?
How many rows should be displayed? You could try messing with the ValueList()/ValueList() fake dimension options, but I would consider loading some derived field that could drive the display of rows.