I would like to ask if there is any way to make a chart straight from connector only, data source from power query in excel. The dataset will be pretty big (more than 1 million records) so I don’t want to have the data in a spreadsheet. If there is no way can someone suggest a different way to tackle this problem? The dataset is coming from MS Access.

Thank you

CodePudding user response：

You can't create a chart from a connection only query but you could load the data to the Excel data model and create a pivot chart from that. Power Pivot uses the Vertipaq engine to highly compress the data and depending on the cardinality of your data, could compress to very small sizes.

However, the correct answer is that you're not going to plot 1 million data points on a single chart so aggregate the data as required in Power Query and plot from that.