Power Query
Example: Utilizing Power Query to deal with large data set (~28million records).
Overview:Here we are looking at NYC green taxi trips for multiple years. Our challenge is Excel only allows for ~1M records while our data set has over 28M records. We need a way to pull in all the data into a pivot table and look at each year individually so we can compare the differences across years. Below is the data set and the years included.
Data: NYC Taxi Trips*
Trip records from all green taxis in NYC from 2017 to 2020.
We can see 4 files that house our Taxi data split by year. Each file has more records then excel can handle--meaning each file cannot even be opened in Excel. But we need to make use of all of them.
Let's say you want to summarize or compare different metrics available in the total dataset year over year. Each standalone file is too large to to be used conventionally with Excel, but with Power Query you can look at year over year data in a familiar-looking pivot table with ease. You can find an example below.
Although there are 28M records of data, you can easily access whatever information needed across years, utilizing a pivot table. This means that you can also utilize pivot charts for a more visual representation of the data (see Charts/Dashboards section)
As you can imagine this opens up a lot of options. Consider if you had weekly financial data that you wanted to look at week over week trends. You could use a similiar approach to capture the data weekly into a pivot table or chart to analyze over time the financial data. Since you are not limited by the number of rows in excel you could keep all 52 weeks of financial data in one pivot table.