Fleetsu in Excel – Get Data the Cool Way with Power Query

Although Fleetsu has many beautiful preset reports that can be viewed online, exported and even scheduled to help you drill into your fleet’s automotive data, for a true data nerd, .csv files and tables in the browser only fuel the desire for more data.

One of the simplest ways to take the data and run with it is to leverage the power of Microsoft Excel and more specifically Power Query. For those interested in Microsoft’s official documentation, you can find it here, but for those looking for a Fleetsu-specific tutorial we’ll get stuck in now.

Getting Data in Excel using Power Query

Now for those of you who don’t know, Power Query is also the tool behind the well known data analytics tool Power BI. As such, the instructions that follow are also applicable to Power BI as well. 

Step 1 – Get Data

Launch Microsoft Excel and select the Data tab from the ribbon at the top.

Next, find the Get Data dropdown menu from the Get and Transform Data group on the left-hand side, and then select From Other Sources followed by From Web.

Get Data → From Other Sources → From Web

In the popup box that appears, paste the API path to the data that you want to work with and click OK.

For information about how to scope your API query to the data-set that you are interested in, all of Fleetsu’s REST API Documentation is freely available.

In this example I am grabbing all truck assets from my demo host – https://demo.fleetsu.com/v1/assets?type=truck

Step 2 – Authentication

After clicking, you will be redirected to a page called Access Web content to supply authentication. Select Basic and enter your Fleetsu username and password before clicking Connect.

Step 3 – Navigate to List

Now that we’re authenticated, there are a few small steps to take before we can begin wading around in the data.
 

As our primary focus for this tutorial exercise is just grabbing data back into Excel, the following steps will show you how to quickly move things into a familiar excel-like table. 

If the previous steps have been followed, your screen should resemble the above, which mirrors directly what we would see from the API if we were to make an API call by typing directly into our browser (see below).

Although it doesn’t look like a button, clicking on the text (not the cell) of the word List will reveal those six records.

Now we’ve gotten them to show that they are indeed records, but the actual data is behind one last door.

Step 4 – Convert to Table

To expand those records navigate to the top left-hand corner of the screen and click on Into Table

Let’s stick with the defaults and click OK

Step 5 – Expand Columns

It may seem like a small achievement, but the appearance of a small expander button in the top right of the record list is significant – click on it.

Step 6 – Columns, lots of Columns

That rush of air you can feel is from the huge list of columns that have just appeared. Pick whatever strikes your fancy.

I recommend unchecking “Use original column name as prefix” because it stops the column names from being prefixed with the table name (eg. “Column1.deviceId”).

When you’ve selected all the columns you want click OK

Boom! This is starting to look like real data.

Step 6 – Close and Load

To grab the data and run back more familiar territory in Microsoft Excel, simply click Close & Load in the top left of the Home tab within the ribbon.

Conclusion

You are now free to manipulate the data to your heart’s content, with the kicker being that if you want to update your data from the API all you need to do is hit the Refresh All button from the Data tab in the ribbon. You don’t need to repeat the previous steps each time.

Now go out and get some data!

TL:DR

The Steps – (This is the quick reference so you don’t have to go line by line in future)

1. Data Tab – Get Data – From Web
2. Authentication Select Basic with Fleetsu Credentials
3. Power Query – Click List
4. Convert to Table
5. Expand Columns
6. Close and Load