Connecting Connectwise Manage to PowerBI

Introduction and Acknowledgements

Yesterday at about 4PM I downloaded PowerBI for the first time. Since we just got some licensing issues squared away, I was interesting in learning how it could replace BrightGauge. By 4:30PM, people in the MSPGeek Slack had pointed me to Comer Technology’s Blog Post on how to quickly connect to CWM’s REST API and pull down data even if it is multi-paged. That post taught me about functions, and using that newly learned information, I abstracted away the API to make it so simple to add data sources even Doug could do it! Comer’s post was a great start, but I did a fair bit a streamlining.

Caveat Emptor

As previously mentioned, I literally started with PowerBI yesterday. There’s a fair chance I’m doing some dumb things, feel free to post the dumb things I did in the comments of the forum post that will be linked in the additional details section below. Also, I’m going to assume you already know how to, and can, poll the CWM API with something like curl, Postman, or PowerShell.

Two Functions to Rule Them All

This system requires 2 functions which our queries will call. This simplifies greatly what it takes to create a new query. Take the names with a grain of salt, you may want to rename them in your implementation. I went with descriptive names instead of functional ones.

Function One: GrabPage

Grab page does literally that. You pass it a page number, the API endpoint, conditions, and childConditions. You generally wouldn’t call this directly, but the next function uses this one as it pages through the data.

Create a new query as a type of Blank Query

Then click on the Advanced Editor button

Replace the existing code with this, changing the [AuthKey] and [ClientID] to match your API info. Also replace the api-na.myconnectwise.net section with your CWM API server address. For more information on the CWM API and how to get this information, see ConnectWise’s developer website.

(PageNumber as number, Path as text, Conditions as text, ChildConditions as text) => let
Source = Json.Document(Web.Contents("https://api-na.myconnectwise.net/v4_6_release/apis/3.0/", [RelativePath=Path &"?conditions=" & Conditions & "&childConditions=" & ChildConditions & "&pagesize=100&page=" & Text.From(PageNumber), Headers=[Authorization="Basic [AuthKey]",clientID="[ClientID]", ContentType="JSon"]]))
in
Source

What this does is create a function that takes our 4 variables and uses it to make the API request. Rather simple and straight forward. Go ahead and rename the query to wrap it up. I named mine CWMGrabPage, which will be important in the next function

Function Two: PageThrough

Page though is considerably more complicated. What this function does is loops through the pages until it gets no more results. If I knew a little bit more about PowerBI scripting, I might have it loop until the return was less than the max page size. After it finishes looping, the return data gets combined into one single list, and that list gets converted to a table for easy digest for the queries we run.

Just as before, create a new Blank Query and go to the Advanced Editor, then paste this code, make sure to update the CWMGrabPage function names if you chose a different name for your function.

(Path as text, Conditions as text, ChildConditions as text) => let Source = List.Combine(List.Generate( ()=> [Result= try CWMGrabPage(1,Path,Conditions,ChildConditions) otherwise null, PageNumber = 2], each List.Count([Result]) >1, each [Result=  try CWMGrabPage([PageNumber],Path,Conditions,ChildConditions) otherwise null, PageNumber = [PageNumber] +1], each [Result])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Now we have a usable function. There’s probably a way to concatenate both functions into a single function, but again, brand new at this.

Rename your function. I chose CWMPageThrough. Although something like CWMAPIQuery might be a better description for people who don’t know how it functions, but just want to use it.

Query Time!

Alright, now that we the functions setup, we can create a query one of two ways. We could create a new blank query, and manually call the function. That’s easy enough, but PowerBI is kind enough to give us a form to do it. Go to the CWMPageThrough, or whatever you named it and fill out the variables, putting a space in anything you don’t want to put a value in and press Invoke.

That will create a fresh function called “Invoked Function”. It wont return any data though, you have to go into the editor and remove the blank space you put into the childComponent field.

Once you remove the space and click out, you should have a table of records which you can explode out into columns by clicking on the double arrow button on Column1. And start playing with your data source. It’s that easy. Start going crazy creating queries! I recommend creating some Query Groups to keep things organized.

Additional Details

You can find CW’s developer documentation here

Comer’s website was a big help in getting my started.

Have feedback? Talk to me in this forum post.