Now, let's analyze your trends alongside big data patterns. Okay. Array Formulas. An array is a range of cells. If you want to group the values of your cells together in a particular order, you can use arrays in your spreadsheet. Let's build a new array and reposition columns. Let's say we wanted to organize the release date column first, then the movie title column second, and lastly the box office revenue column. Now these three columns are repositioned and rearranged in our Sheet. Let's go further. Here's another example. Let's say you need to figure the profit of each of these movies. You can have the array formula calculate the difference between the box office revenue column and the budget column to determine the profit. This saves a lot of time because you can streamline calculations for a big range of data instead of having to do calculations in individual cells. All right. Now let's talk about a super exciting feature in Google Sheets: BigQuery. With a BigQuery, Sheets has the ability to support datasets that include billions of rows. Yes, I said billions of rows. So Sheets can connect to large datasets like BigQuery. BigQuery is a cloud-based data warehouse from Google that houses massive datasets. Keep in mind that only premium enterprise users who pay for G Suite will be able to access these features, but I'm going to give you a sneak peak of what it can do. So we're going to be quickly exploring a dataset of New York City taxi cab data. It has a 112 million rows of data. Let's imagine that you still have your movie producer hat on. You're making that romantic comedy. For the final scene of the movie, the director wants the main character to stop the love interest from getting into a cab that's headed for the airport. For this big finale, the director wants you to look up the most popular pickup locations for New York City cabs. This is the spot where your crew will literally stop traffic for the two actors who will engage in a final embrace amid honking horns. So let's find the most commonplace that New Yorkers get picked up by taxis. First, let's connect our spreadsheet to BigQuery. Here we can see some sample public datasets and we could experiment with any of these examples. But for our purposes, we're going to analyze this data about yellow taxis in New York City. Once you've connected to a table in BigQuery, you'll see many of the same features you can use on native Sheets data: functions, charts, pivot tables. Again, I want to highlight that the connection between Sheets and BigQuery allows you to find trends among millions of data points. It's pretty powerful. Okay. Let's do an analysis by creating a chart. So to do that, let's click on the chart icon and we'll insert to a new sheet. On the left, we see a placeholder here for our chart and on the right we have our chart editor there. In this example, we want to find out the most common pickup location in New York City. Remember, this is where our two actors will share their final embrace. We're going to create a bar chart from this column. Based on our bar chart, we can then see here the most popular place to get picked up by a taxi in New York City. Okay. Now let's try doing an analysis using the pivot table. Let's find out the most common time of day for pickups. This will help our director determine whether to shoot a sunny morning or a rainy night for dramatic effect. Okay. So let's select pivot table, select rows, click on values, and select apply to run. There we go. Everything you've seen so far has shown how to do analysis on data in BigQuery. So when you're creating a chart, table, or function, you're analyzing data that lives in BigQuery. However, if you want to import data from BigQuery into your spreadsheet, you can do that using extracts. So let's do that now. Click on extract up here and we'll open up a new sheet. In our extract editor over here, we can choose which data or columns we want to extract. Here, we're going to choose the pickup_datetime, the dropoff_datetime, the total_amount, the pickup_location_id, and the dropoff_location_id. We can also customize the data we want to extract with filters, and conditions, and specify how much data we want to import from BigQuery. As our row limit, let's extract 10,000 data points. So let's say we've selected a location where actors will share their last embrace of the film. But a month before we shoot on location, we want to make sure that the site is still in most popular or if there's somewhere else that's better. In that case, we can import new data into our existing cells. Now that we've entered the data, it's now editable. Okay. Cool. Next up, test time.
