CW Manage: Getting Started with PowerBI (The Update!!)

Quinntin Comer Uncategorized 10 Comments

This is an update to a previous post. See the original here: https://comertechnology.com/cw-manage-getting-started-with-powerbi/

One thing that really had been bugging me was the absence of the capability of passing a URL test in the previous data source. In this article, I have the updated data sources as well as an explanation of what changed.

Useful Information/Links

  • Postman – Postman makes getting started with API and API Development a breeze. For a newbie, they make it very easy and there is a lot of documentation and guides out there surrounding Postman in use for testing and developing with numerous APIs.
  • ConnectWise Developer Network – This website provides access to the API library, database library and everything else you could desire for getting started with developing/automating/reporting from the ConnectWise suite. Best part — its FREE.
  • MSPGeek (Formerly LabTechGeek) Community – This community is a good resource for “all things ConnectWise” and is a fantastic resource for anyone new to the community.
  • Base64Encode – This website is useful for generating your authorization token instead of using Basic authentication.

Requirements

  • PowerBI (obviously) – If you are going to programatically refresh data or schedule data to refresh – when using an API/JSon datasource you need to configure an “on-premise data gateway”. Power BI will reference the gateway as a source which will reference the web source.
  • CW Manage API Member – https://comertechnology.com/getting-started-with-cw-manage-rest-api/
  • Base64 Conversion of the Public and Private key (Recommended, not required)
  • ConnectWise Client ID – You must sign up on the developer website and have a “Client ID”. https://developer.connectwise.com/ClientID

Process

  1. Convert your Basic authentication to an authorization token.
    1. Go to http://base64encode.org > Type in “CompanyID+PublicKey:PrivateKey” and encode it. This is your authorization token.
  2. Create a new Dashboard in Power BI
    1. Press Get Data > Select “Blank Query” (I will provide you a query below).
      1. In this example, we will use two queries. The API has a maximum return of 1,000 records. To get around this we will have an initial query to loop through all the pages until there are no more and a second to use that as our source.
      2. Right Click > on the new query.
        1. Select “Rename” > Name: PageData
        2. Select “Advanced Editor” and paste the following code. You will need to modify the following sections:
          1. <BOARDIDHERE> – If you want to specify a board, if not you can remove “?conditions=Board/id=x”
          2. <AUTHORIZATIONHERE> – This is the Authorization token you generated by encoding your CompanyID+PublicKey:PrivateKey to Base64
          3. <CLIENTIDHERE> – This is the Client ID you have generated at the CW Developer site.

            let Source = (PageNumber as number) => let Source = Json.Document(Web.Contents("https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets",[RelativePath="?conditions=board/id=<BOARDIDHERE>&pagesize=1000&Page="&Number.ToText(PageNumber),Headers=[Authorization="Basic <AUTHORIZATIONHERE>",clientID="<CLIENTIDHERE>", ContentType="JSon"]])) in Source in Source

          4. What changed? Well, we changed the URL in the “Web.Contents” section to reference a “Relative Path”. This change made it so that anything in the static URL path is used for the “URL test” that Power BI does during a refresh. Then, the Relative Path is added onto the end including the page number. Since we are using variables in our URL, Power BI could not verify the original URL since it wasn’t “static”.
        3. Right click in the “Queries” section > Click New Query > Blank Query.
          1. Right click on our new query and rename it as you wish.
          2. Right click > Advanced Editor. This is where will specify our data from the PageData source. You can find the code at the bottom under “AllData”.

      3. In this case, I have already converted the results to a table and expanded several columns. You can modify these to your choosing or start from scratch.
      4. Press Close & Apply.
      5. The data will refresh and you are ready to create your visualizations.

PageData Code

AllData Code

Examples

We can create a basic table showing all “New” tickets with the below. Now, we have a number of statuses that are duplicates (i.e., New and New*). In our case, anything with an asterisk has a notification step. So, you can use a new column with “Fuzzy Matching” to concatenate the two statuses.

  1. Select the “Table” visual.
  2. In “Filters on this visual” > Drag “Status” over. Select the status you want to filter on. In our case, New*.
  3. Under Visualizations > Values (after selecting the table) > “Add data fields here” > Drag the Fields you would like to be displayed. Or, just check them.
    1. Voila! You have a basic table containing the columns you selected and filtering on the “New” status.

Comments 10

  1. Hi there,

    I was trying to add dateEntered, which seems to be under _info and the closedFlag and closedDate.

    I adjusted your formula as below but get “Expanded _info” wasn’t recognized…. any pointers on my interpretation of this to get me on the right track?

    let AllData =
    let Source = List.Generate( ()=> [Result= try PageData(1) otherwise null, PageNumber = 1], each List.Count([Result]) >1, each [Result= try PageData([PageNumber]) otherwise null, PageNumber = [PageNumber] +1], each [Result]),
    #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Expanded Column2″ = Table.ExpandListColumn(#”Converted to Table”, “Column1″),
    #”Expanded Column3″ = Table.ExpandRecordColumn(#”Expanded Column2”, “Column1”, {“id”, “summary”, “recordType”, “board”, “status”, “company”, “site”, “addressLine1”, “addressLine2”, “city”, “stateIdentifier”, “zip”,”type”,”subType”,”priority”,”serviceLocation”,”contact”,”parentTicketId”,”closedFlag”,”closedDate”,”_info”}),
    #”Expanded status” = Table.ExpandRecordColumn(#”Expanded Column3″, “status”, {“name”}, {“status”}),
    #”Expanded Board” = Table.ExpandRecordColumn(#”Expanded status”, “board”, {“name”}, {“board”}),
    #”Expanded type” = Table.ExpandRecordColumn(#”Expanded Board”,”type”,{“name”}, {“type”}),
    #”Expanded subType” = Table.ExpandRecordColumn(#”Expanded type”,”subType”,{“name”}, {“subType”}),
    #”Expanded company” = Table.ExpandRecordColumn(#”Expanded subType”,”company”,{“name”}, {“company”}), #”Expanded priority” = Table.ExpandRecordColumn(#”Expanded company”,”priority”,{“name”}, {“priority”}),
    #”Expanded location” = Table.ExpandRecordColumn(#”Expanded priority”,”serviceLocation”,{“name”}, {“serviceLocation”}), #”Expanded contact” = Table.ExpandRecordColumn(#”Expanded location”,”contact”,{“name”}, {“contact”}),
    #”Expanded _info” = Table.ExpandRecordColumn(#”Expanded _info”,”_info”,{“dateEntered”}, {“_info”})
    in
    #”Expanded contact”
    in
    AllData

    1. Post
      Author

      Here is the updated code that should get you what you’re looking for:

      let
      AllData = let
      Source = List.Generate( ()=>
      [Result= try PageData(1) otherwise null, PageNumber = 1],
      each List.Count([Result]) >1,
      each [Result= try PageData([PageNumber]) otherwise null, PageNumber = [PageNumber] +1],
      each [Result]),

      #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #”Expanded Column2″ = Table.ExpandListColumn(#”Converted to Table”, “Column1″),
      #”Expanded Column3″ = Table.ExpandRecordColumn(#”Expanded Column2”, “Column1”, {“id”, “summary”, “recordType”, “board”, “status”, “company”, “site”, “addressLine1”, “addressLine2”, “city”, “stateIdentifier”, “zip”,”type”,”subType”,”priority”,”serviceLocation”,”contact”,”parentTicketId”,”_info”,”closedFlag”,”closedDate”}),
      #”Expanded status” = Table.ExpandRecordColumn(#”Expanded Column3″, “status”, {“name”}, {“status”}),
      #”Expanded Board” = Table.ExpandRecordColumn(#”Expanded status”, “board”, {“name”}, {“board”}),
      #”Expanded type” = Table.ExpandRecordColumn(#”Expanded Board”,”type”,{“name”}, {“type”}),
      #”Expanded subType” = Table.ExpandRecordColumn(#”Expanded type”,”subType”,{“name”}, {“subType”}),
      #”Expanded company” = Table.ExpandRecordColumn(#”Expanded subType”,”company”,{“name”}, {“company”}),
      #”Expanded priority” = Table.ExpandRecordColumn(#”Expanded company”,”priority”,{“name”}, {“priority”}) ,
      #”Expanded location” = Table.ExpandRecordColumn(#”Expanded priority”,”serviceLocation”,{“name”}, {“serviceLocation”}),
      #”Expanded contact” = Table.ExpandRecordColumn(#”Expanded location”,”contact”,{“name”}, {“contact”})
      in
      #”Expanded contact”,
      #”Filtered Rows” = Table.SelectRows(AllData, each ([company] <> “Catchall”)),
      #”Added Custom” = Table.AddColumn(#”Filtered Rows”, “Full Location”, each [city]&”, “&[stateIdentifier]&” “&[zip]),
      #”Filtered Rows1″ = Table.SelectRows(#”Added Custom”, each true),
      #”Expanded _info” = Table.ExpandRecordColumn(#”Filtered Rows1″, “_info”, {“dateEntered”}, {“_info.dateEntered”})
      in
      #”Expanded _info”

      Basically, in this section “#”Expanded Column3″” – I added the columns you wanted (_info, closedDate,and closedFlag).

      Then in the last step we expanded _Info

      Let me know if that helps at all.

  2. Hi Quinntin,

    I’m trying to get AllData back to square 1 where I can convert it to a table and expand/manipulate all of the columns but I’m clearly doing something wrong as I am getting a token in expected error.

    Here’s what I was attempting,

    let AllData =
    let Source = List.Generate( ()=> [Result= try PageData(1) otherwise null, PageNumber = 1], each List.Count([Result]) >1, each [Result= try PageData([PageNumber]) otherwise null, PageNumber = [PageNumber] +1], each [Result])
    in AllData

    1. Post
      Author

      Hi, Will.

      For each “let” statement you will need a matching “in”

      So, it should look like this: https://i.imgur.com/OJ7uhVT.png

      let AllData =
      let Source = List.Generate( ()=> [Result= try PageData(1) otherwise null, PageNumber = 1], each List.Count([Result]) >1, each [Result= try PageData([PageNumber]) otherwise null, PageNumber = [PageNumber] +1], each [Result])
      in
      Source
      in
      AllData

      Once you do this you will need to go to “Transform” and transform the result to a table and then expand the table with the columns you’d like.

      1. Hi all I tried this and managed to get everything connected but I am still struggling to get data in from a query so looked at the one above to draw back to basics.

        let AllData =
        let Source = List.Generate( ()=> [Result= try PageData(1) otherwise null, PageNumber = 1], each List.Count([Result]) >1, each [Result= try PageData([PageNumber]) otherwise null, PageNumber = [PageNumber] +1], each [Result])
        in
        Source,
        AllData1 = AllData{0}
        in
        AllData1

        But all I get is the error and unsure why this has added the AllData{0} on I am new to PowerBI so learning everyday

        Expression.Error: We cannot convert the value null to type List.
        Details:
        Value=
        Type=Type

        1. Post
          Author
  3. Does this method not work when publishing to Power BI web? I have it working in PBI Desktop and publish it successfully but it says invalid credentials when I try to refresh the data on the web. Looking for a “lite” alternative to a full-blown Enterprise Gateway setup for clients in which that’s overkill.

    1. Post
      Author

      Yes, it does! Thats why the updated article was released. Previously, you could not since the URL was not static. In the way this is built there is a static URL up front so Power BI can connectivity test it and then applies the query and relative path.

      Make sure your permissions for the data source are set to “public” and in the scheduled refresh screen, make sure you have unchecked “required connectivity check” (I dont remember the exact words). Email me using the contact form and I’d be happy to help you more. Love what you guys do and you’ve been a huge inspiration for me for a long time! https://i.imgur.com/nBAIS5Z.png

  4. Hi Quinttin,

    I am having trouble getting this connection up and running. The PageData query seems to work fine, but the second query gives me an error:

    Expression.Error: We cannot convert the value null to type List.
    Details:
    Value=
    Type=[Type]

    I literally just copied and pasted in your code and made the changes as you suggested (removed board ID, added in the auth token and the client ID), as that’s about the extent of my knowledge with this. So I am not sure where the issues is. Here is my code:

    PageData code:
    let
    Source = (PageNumber as number) => let
    Source = Json.Document(Web.Contents(“https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets”,[RelativePath=”&pagesize=1000&Page=”&Number.ToText(PageNumber),Headers=[Authorization=”Basic dGhlaXRjbytxRFNMb05GbUphV3BlYncxK3pXUmFsU2dqVHJORzRSQ2w=”,clientID=”PUIWSP58″, ContentType=”JSon”]]))
    in
    Source
    in
    Source

    Query code (this is the one that throws the error:
    let AllData =
    let Source = List.Generate( ()=> [Result= try PageData(1) otherwise null, PageNumber = 1], each List.Count([Result]) >1, each [Result= try PageData([PageNumber]) otherwise null, PageNumber = [PageNumber] +1], each [Result]),
    #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Expanded Column2″ = Table.ExpandListColumn(#”Converted to Table”, “Column1″),
    #”Expanded Column3″ = Table.ExpandRecordColumn(#”Expanded Column2”, “Column1”, {“id”, “summary”, “recordType”, “board”, “status”, “company”, “site”, “addressLine1”, “addressLine2”, “city”, “stateIdentifier”, “zip”,”type”,”subType”,”priority”,”serviceLocation”,”contact”,”parentTicketId”}),
    #”Expanded status” = Table.ExpandRecordColumn(#”Expanded Column3″, “status”, {“name”}, {“status”}),
    #”Expanded Board” = Table.ExpandRecordColumn(#”Expanded status”, “board”, {“name”}, {“board”}),
    #”Expanded type” = Table.ExpandRecordColumn(#”Expanded Board”,”type”,{“name”}, {“type”}),
    #”Expanded subType” = Table.ExpandRecordColumn(#”Expanded type”,”subType”,{“name”}, {“subType”}),
    #”Expanded company” = Table.ExpandRecordColumn(#”Expanded subType”,”company”,{“name”}, {“company”}), #”Expanded priority” = Table.ExpandRecordColumn(#”Expanded company”,”priority”,{“name”}, {“priority”}),
    #”Expanded location” = Table.ExpandRecordColumn(#”Expanded priority”,”serviceLocation”,{“name”}, {“serviceLocation”}), #”Expanded contact” = Table.ExpandRecordColumn(#”Expanded location”,”contact”,{“name”}, {“contact”})
    in
    #”Expanded contact”
    in
    AllData

    Appreciate the help, if you can give it

    1. Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *