DEV Community: Matt Thacker

Working With APIs in Microsoft's Power BI

Matt Thacker — Thu, 09 Sep 2021 11:22:23 +0000

Microsoft's Power BI is a powerful tool for data analysis and data visualization. For all of it's shortcomings, one of its most appealing features is its accessibility; from the individual to corporate enterprise integration.

In the recent weeks, I've encountered a similar question accross forums and that is: How do I work with an API in Power BI? Hence, the purpose of this post!

There are a couple of ways to work with APIs in Power BI, but the method that I reach for most is Python's Request module. Below, we'll step through the process of installing the necessary Python packages, enabling Python in Power BI, and getting data from the US Federal Election Commission's API.

Installing the Necessary Python Packages

For the purposes of this post, I'm assuming you already have Python installed on your computer. If not, you can download the latest version from Python's website.

The two main Python packages you will need is Requests and Pandas. Both Requests and Pandas can be installed using pip install. You can install both libraries with the same pip install command by seperating the two with a space.

Next, you'll want to make sure your Power BI is pointed to the correct file path to run Python scripts. Open a new instance of Power BI and navigate to File and Options and settings.

Constructing the API Request

The next step is to construct the API request. I normally start writing my API request in a code editor before running it as a script in Power BI. Again, we'll be using the US Federal Election Commission's API for this GET request. It is an open API but you will need to create an account to obtain and API key, which you can do at the FEC website.

import requests
import pandas as pd

We start by importing the necessary libraries to our Python file. If you're interested in learning more about the respective libraries, you can find the full documentation at Requests and Pandas. It's convention to import pandas with the alias of pd to prevent having to write pandas everytime we want to use it.

Next, we'll assign our API key to a variable, key. In most instances you can create an API request with just a couple lines of code.

key = 'some_key'

url = f'https://api.open.fec.gov/v1/candidates/?api_key={key}&page=1&sort_hide_null=false&sort_null_only=false&sort_nulls_last=false&per_page=100&sort=name'

r = requests.get(url).json()

That's it. That is all you need to make a GET request to an API. Most APIs are stored in the form of JSON files, that is why we need the .json() method. Make sure to read the documentation for the API you're using. It will describe how the data will returned to you.

For this API call, we're going to write it a bit different. The FEC API results are paginated; meaning the results are returned as a per page. We're going to utilize a for loop to retreive the first 500 results (100 per page, which you can see is a parameter in the url string).

The code above creates a function called api_call that accepts a parameter num and performs the basic GET request from earlier, substituting the page number in the url string with the parameter that's passed into the api_call function. Below is what is returned when we make the GET request.

{
  "api_version": "1.0",
  "pagination": {
    "per_page": 100,
    "count": 44523,
    "pages": 2227,
    "page": 1
  },
  "results": [
    {
      "state": "US",
      "candidate_status": "N",
      "last_file_date": "2021-05-05",
      "federal_funds_flag": false,
      "load_date": "2021-07-27T06:29:31+00:00",
      "party": "PAF",
      "cycles": [
        2022
      ],

We want the results key, so we will store that in a DataFrame object. This is where Pandas comes into play. Power BI works best with Pandas' DataFrames. Within the api_call function, we store each request in a variable df and return that variable.

Next, we'll create a for loop to loop through the first 5 pages and append each result to an empty list called frames. After the for loop has ran, we'll create a new variable to store the final DataFrame and use the .concat() method and pass in the frames list. Using the .concat() method without an axis kwarg will append the DataFrames to one another.

We'll run df_final.info() to see the results:

And...it worked! We can see we have 500 entries contained in this DataFrame.

Our last step is to take that code and paste it into Power BI.

In Power BI, click Get Data, and type Python as the data source. The Python script editor will open and you can paste your code into that editor and click okay.

After the script has run, we'll get the Power Query Navigator to select which data we want to load.

We'll Transform Data to open in the Power Query Editor and make any ETL adjustments needed.

And that is it. If you look at the source step in the Power Query editor, you'll see your Python script is stored. Which means each time you refresh the data, the script will run the API request again and produce the most up to date information.

Practice

For a fun project to go your skills, use the pagination key in the oringial result and extract the count of total pages in the request. Use that as the top end of your for loop to retreive ALL the data.

Final Thoughts

I hope you've found this at least somewhat helpful. I love working with APIs, it's really want got me into coding in the first place.

Also, I don't write a lot, but I woould love to start doing this more. I'd love feedback on content and writing style, tips and tricks, whatever.

Happy coding!!

List.Accumulate()

Matt Thacker — Sun, 29 Nov 2020 08:24:57 +0000

Power Query's M Language's Equivalent to JavaScript's Reduce

A Quick Intro...

If you're not familiar with the Microsoft Power Platforms, Microsoft Power has two incredibly rich, robust, and dynamic languages that, when working in tandem, can create some of the most sophisticated data processing tools available to the average end-user. These languages are M (Power Query) and DAX (Power Pivot, Power BI, etc.)

I wanted to start this first post with a really powerful function in M called the:

function List.Accumulate()

The reason I wanted to showcase this M function first is because it behaves exactly like JavaScript's array.Reduce() method. It's been my experience that there are a lot of people whom have dedicated their time to master languages like M and DAX and are deemed 'not real coders.' It's my goal to get that one Excel guru who doesn't think he knows enough to learn to program to see it's really just about syntax.

List.Accumulate()

I think the easiest way to understand List.Accumulate is to break it down from its technical definition, whish is;

Accumulates a summary value from the items in the list, using accumulator. An optional seed parameter, seed, may be set. -Microsoft Docs

Or, even better, the Syntax description:

List.Accumulate(list as list, seed as any, accumulator as function) as any

Yes, while each of these are true, what in the world does it say?

Here's the way it's easiest for me to remember it:

'list as list' - A list in M is the equivalent to an array in JavaScript and is created by wrapping a list of items within { }
'seed as any' - This is your starting point (I'll explain this part later)
'accumulator as function' - This is a function that takes two parameters and executes the code within the function
And finally reduces that list down to one value

Below, I've written out a very basic List.Accumulate function in Power Query;

The List = {10, 20, 30, 40, 50}
Seed (or starter) = 0
Accumulator Function = (state, current) two parameters => state + current (add the two parameters together)

Array.Reduce()

The Reduce method in JavaScript is a perfect way to step through each individual step of what's happening with List.Accumulate.

In the snippet below, a is a variable with an array assigned to it (same numbers as the previous example).

let a = {10, 20, 30, 40, 50}

Next is a function called totalA that also takes two parameters, state and current. Only now, I've console.logged both state and current so we can see what happens through each iteration of the function.

function totalA(state, current) {
    console.log(`State is equal to ${state}`);
    console.log(`Current is equal to ${current}`);
    console.log(`------------`);
    return state + current;
}

Lastly, a new variable c to hold the reduce method. Here, reduce is taking two arguments: the callback function totalA and a second, optional, argument of 0.

let c = a.reduce(totalA, 0)

console.log(c)

Here is why that optional second argument in the reduce method makes a difference: it changes the starting point of your function. With the 0, that 0 is passed as the first value into state and current is passed the first value of the array (10). Without the 0, the 10 is passed as the value of state and 20 as the value of current. (Worth noting, the starting value is not optional in List.Accumulate)

But remember, the callback function is returning the state + current through every itteration, just like with the List.Accumulate.

I hope some of you found this helpful. Hope everyone is having a great weekend!

JavaScript - Map & Filter

Matt Thacker — Tue, 23 Jul 2019 11:45:48 +0000

I must start by saying I love the .map, .filter, and .reduce methods in Javascript. However, for quite sometime, and still now, I occasionally find myself flipping back to MDN or W3 to look something up. That’s why I decided my next post would be a walkthrough to make me better remember these amazing tools.

.Map() Method

The JavaScript Map method iterates over every element in an array and returns a new array with the result. The .map() and .forEach() methods appear to have the same functionality; the key difference is .map() ability to return a new a new array.

Syntax

The textbook syntax for the .map() method is array.map(callbackFunction(currentValue, index, arr), thisValue).

Let's take each element by itself:

array - any JavaScript array, i.e. let myArr = [1,2,3,4,5]
map() - the method being called on myArr
callbackFunction - a function, either name, anonymous, or arrow, that will iterate over each item in myArr
index - (OPTIONAL) the index of the element in the array being processed
arr - (OPTIONAL) the array the map method is being called on
thisValue - (OPTIONAL) value to use for this when executing the callback (we'll get to this at a later post)

As I said, the above syntax is "textbook." Below are three simple ways to utilize the .map() method.

var b utilizes an anonymous function
var c utilizes a named function
var d utilizes an arrow function

The .map() method does not mutate the original array. In the code above, a still evaluates to [1,2,3,4,5]. If you are not using the new array or if you are not returning any values from the callback, .forEach is probably the better option to use.

.Filter() Method

The .filter() method, like .map(), returns a new array from the criteria passed to each item of the original array. Filter also uses a callback function. The best way for me to process how the callback function in .filter() works is, the callback function is criteria that each item of the array is checked against. If the item evaluates to true, that item is "filtered" into the new array.

Syntax

The syntax for the .filter() method is the same as .map().

array.filter(callbackFunction(currentValue, index, arr), thisValue).

array - any JavaScript array, i.e. let myArr = [1,2,3,4,5]
filter() - the method being called on myArr
callbackFunction - a function, either name, anonymous, or arrow, that will iterate over each item in myArr
index - (OPTIONAL) the index of the element in the array being processed
arr - (OPTIONAL) the array the map method is being called on
thisValue - (OPTIONAL) value to use for this when executing the callback (we'll get to this at a later post)

Again, below are different examples utilizing the .filter() method:

One thing to note, as I said, filter returns a new array EVEN IF no items in the original array meet the criteria. If no items pass, an empty array is returned.

Below is an example in which Filter could be used in a real-life example:

Here, an array of cities is listed (even though this is a short array, the concept can be extrapolated with larger Fetch requests). Next is a function that takes an array and searches criteria as arguments and returns a filtered array based on the search parameter. In this example, the cities array is being passed, and being asked to return all cities in that array that contain the letter "a".

There is another method that is typically paired when talking about map and filter and that's reduce. However, there's a lot to the reduce method and I think it deserves its own post.

With that, I hope someone finds this tutorial helpful, as I certainly did. I've only been posting a short time, but I've already found myself coming back to my post on destructuring to keep it fresh in my mind.

As always, happy coding!

ES6 - Destructuring the Good Parts!

Matt Thacker — Sat, 06 Jul 2019 05:01:30 +0000

I've been learning JavaScript for a couple of months now. To break up tutorials and reading, I love trying my hand at coding challenges. I have decent success in completing the challenges, however, my code will usually be 3 to 4(x's) longer than the solution others come up with. And if we're being honest, I didn't have the slightest clue as to what most of the code base for the solutions were doing.

Recently, I started learning some of the additions to ES6 and I'm not sure how I've made it this far without knowing several of these. Some I really struggled to understand and I probably still don't FULLY grasp the technical aspect or the full capabilities but this is why I'm writing this. I last posted that I wanted to use my learning as a resource to help others that struggle in learning the same way that I do.

I debated on what to start posting about, and I've finally decided to start a series of posts about, what I think are, the best parts of ES6.

Let's Get Destructuring {Objects}!

What is destructuring? It's essentially a fancy term for being able to pull out a value(s) from an array or properties from an object and save it to a variable. A lot of the coding challenges I've completed have some variation that required this task in order to fulfill its requirements.

This is how I've always completed extracting values from arrays and objects before:

If we console.log(myName) we'll get the value "Matt" for the name property.

Because I have nested objects, if I wanted to get the value for my favorite Premier League soccer team, I'd need to create a variable that held myObj.likes.soccer.Premier. And I'd have to do that twice to get my two favorite soccer teams.

With destructuring, we can cut down on the repetitiveness of the code.

Here, I'm essentially creating two variables FIFA and Premier that are the property names within the nested object. We can go a step further and assign the values to our own created variable names by assigning the variable name after the property name: const {propertyName:newVar} = object

Destructuring is a much more effecient way of extracting values out of objects.

Hope this helps someone, because I feel like I understand it more now that I've sat down and written this. Happy Coding!

DEV Community: Matt Thacker

Working With APIs in Microsoft's Power BI

Installing the Necessary Python Packages

Constructing the API Request

Practice

Final Thoughts

List.Accumulate()

Power Query's M Language's Equivalent to JavaScript's Reduce

A Quick Intro...

List.Accumulate()

Array.Reduce()

JavaScript - Map & Filter

.Map() Method

.Filter() Method

ES6 - *Destructuring the Good Parts!*

Let's Get Destructuring {Objects}!

ES6 - Destructuring the Good Parts!