Hello Together,
I have experienced data loss / data cut with using the excel built-in Data Model and power pivot charts, i suspect something is not set up right with the data transition, or i am missing some simple setting. I have made a throughout internet search, but i cannot find any solution for my problem.
Excel version: Microsoft Office 365 ProPlus x64
Data setup: Rows indicate data entries, each entry has an "x" and "y" value, and also some data, filter data. (Mainly i use production statistics, each entry is relevant for a measurement identification, time, station etc..). Often i have to handle big data sets, ranging around few million entries, therefore i use the following set-up
Data source in .csv --> Loaded into Query, set-up and modified in query --> Loaded into Data Model, additional calculations done for calculated fields --> Fed back to Power Pivot chart
Chart set-up: I work with scatter represented data, unfortunately it is not available currently with pivots, so i use line chart with markers, with line visibility turned off.
The problem is the following: I noticed that sometimes the data is not shown by the chart, even though it is selected by the slicers. Good example is that if i want to see 1 month of data with 12 series resulting around ~100t data points, the chart only shows data for the first 10 days of data, the rest is cut off. First i suspected that the line chart cannot represent the data and runs out of limits, but it is not the case, because if i use the normal line chart in excel, it can draw it.
Fortunately i also managed to reproduce the problem with a simple set-up, what i can share.
Sample set-up:
Data: in .csv, delimited with tabulator, 2 Series of data. Each series have the same data entries for "x" and "y" axis.
The data is imported into a query, while selected to load into sheet and also load into Data Model.
From the data loaded into sheet i create a normal line chart, and from Data Model i also create a Pivot Line Chart.
I made 3 examples.
With a data set of 4t (Series 1 - 2t and Series 2 - 2t), there is no issue, both chart show the same.
With a data set of 20t (Series 1 -10t and Series 2 - 10t), there is also no issue.
With a data set of 133.2t (Series 1 - 66.6t and Series 2 - 66.6t) the issue appears, so something is happening in that range.
With 133.2t data, the excel normal line chart show all the data, 66.6t x2, both of the series. However, the Power Pivot Line Chart only show 1064 data point for each of the series. The data is not sent to the line chart.
For the next step if i put a slicer for the "y" values and manually select like 3109 data point, it is sent to the chart, and the points are represented. But of i select "Clear Filters", it goes back to 1064 points.
Question: Is there a setting, where i can force the Power Pivot to always send the data, and do not cut of chunks?
I also attach a test worksheet with data, but it is only the 4t data set. If anyone would like to try, please change the Query source location to the relevant location, increase the data set to up-to 66.6t for testing.
Edit:
I have uploaded the 133.2t file set-up to GoogleDrive, in the following link it can be downloaded. In this set-up only the data .csv location modification is required in the query, everything else can be checked / tested.
Link: The link is in the txt (Example_setup_133.2t_download_link) as the forum does not allow me to put link in the description yet.
Every help is appreciated, this is an issue that has been bothering me in a while.
Thank you!
Bookmarks