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

Quinntin Comer Uncategorized 22 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

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

AllData Code

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

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 22

  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

      1. Thanks Quinntin!

        Updating to Public on both the pbix file and the PBI service Dataset seemed to do the trick. It still shows an error on the dataset page but seems to be refreshing fine.

        Transforming the JSON to a table for the time entry report I was doing took a bit of learning but once I’m finished I’ll have a portable report that doesn’t require any setup for the end user after I get their API connection set up. Awesome!

      2. Thank you very much Quinntin, this was extremely helpful!

        I am, however, trying to publish to Power BI on the web but cannot get the “Data Source Credentials” correct as the only option is “Basic”, “Anonymous”, and “Windows” — none if which allow one to add a custom header for clientID. Without this, I cannot authenticate and, it appears, cannot automatically refresh the data.

        Do you have any idea how to make this work?

        1. The answer to my question appears to be using the “Skip connection test” checkbox within “Data Source Credentials”

      3. Hi Quinntin,

        You are a wizard but I need a little help!!!!

        I have followed your guide (I am based in the uk so I have to use https://api-eu.myconnectwise.net/v2020_2/apis/3.0/service/tickets in the page data section)

        I Then copied the following for the all data section:
        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

        This brings up
        List
        1 Error

        When I try to expand to a table I get the following Error:

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

        Any advice would be amazing

    2. Hi Quinntin,

      You are a wizard but I need a little help!!!!

      I have followed your guide (I am based in the uk so I have to use https://api-eu.myconnectwise.net/v2020_2/apis/3.0/service/tickets in the page data section)

      I Then copied the following for the all data section:
      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

      This brings up
      List
      1 Error

      When I try to expand to a table I get the following Error:

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

      Any advice would be amazing

  4. Hi Quinntin,

    Thanks for this tutorial.
    I don’t suppose you have any suggestions regarding the following?
    I’m also getting the error that James posted last year:

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

    But that’s just from the fact that the PageData query is throwing a 400 error:

    ‘https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets/?conditions=pagesize=1000&Page=1’ (400): Bad Request
    Details:
    DataSourceKind=Web
    DataSourcePath=https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets
    Url=https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets/?conditions=pagesize=1000&Page=1

    Tried replacing v_4-6_release with v2020_1 (no difference)
    Tried without base64 encoding (no difference)
    Tried removing all conditions/filters (no difference, although wouldn’t expect it to)

    Any help appreciated,
    Thanks

    1. I figured it out, just dropping back here with info in case it helps anyone
      I was using an ampersand instead of ‘AND’ as the operator in conditions.

      My correct query when grabbing multiple pages with the AllData query:

      let Source = (PageNumber as number) => let Source = Json.Document(Web.Contents(“https://XXXXURLXXXX/v4_6_release/apis/3.0/service/tickets”,[RelativePath=”?conditions=Company/Id=XXXCOMPANYIDXXXXAND location/id=XXXLOCATIONIDXXX&OrderBy=id desc&pagesize=1000&Page=”&Number.ToText(PageNumber),Headers=[Authorization=”Basic XXXXXAUTHXXXX”,clientID=”XXXXCLIENTID”, ContentType=”JSon”]]))
      in Source in Source

      Or to just grab a single page (not using AllData):

      let Source = (PageNumber as number) => let Source = Json.Document(Web.Contents(“https://XXXXURLXXXX/v4_6_release/apis/3.0/service/tickets”,[RelativePath=”?conditions=Company/Id=XXXCOMPANYIDXXXAND location/id=XXXLOCATIONIDXXX&OrderBy=id desc&pagesize=1000&Page=1″,Headers=[Authorization=”Basic XXXXXAUTHXXXX”,clientID=”XXXXCLIENTID”, ContentType=”JSon”]]))
      in Source in Source

  5. Hi Quinntin,

    You are a wizard but I need a little help!!!!

    I have followed your guide (I am based in the uk so I have to use https://api-eu.myconnectwise.net/v2020_2/apis/3.0/service/tickets in the page data section)

    I Then copied the following for the all data section:
    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

    This brings up
    List
    1 Error

    When I try to expand to a table I get the following Error:

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

    Any advice would be amazing

  6. Hi Quinntin,

    You are a wizard but I need a little help!!!!

    I have followed your guide (I am based in the uk so I have to use https://api-eu.myconnectwise.net/v2020_2/apis/3.0/service/tickets in the page data section)

    I Then copied the following for the all data section:
    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

    This brings up
    List
    1 Error

    When I try to expand to a table I get the following Error:

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

    Any advice would be amazing

  7. Hi Quinntin,

    Amazing doc, Better than some of the connectwise support.

    With the help of your doc I’ve been able to get all the tickets but some of the information is not there such as Date entered, Date completed, who it was assigned to and who it was closed by Etc.

    is there something else I need to add or did I miss something?

    thanks in advanced.

  8. Hey Quinntin, thanks for this detailed piece.
    Quick question: I have the solution working, but I can’t retrieve the “Forecast” information of my opportunity.
    Have any idea what may be the issue?

  9. 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
  10. Hey there!

    Have you had any luck extracting the sales/forecast api so that you can build a PowerBI dashboard using the revenue, margin, etc? The issue I am having with it is it requires the opportunity ID in the URL. I have no trouble pulling the all opportunities from sales/opportunities, but can’t figure out how to pull all forecast data from sales/opportunities/ID/forecast. I can input an ID and get one record but i’d like all of them… Thanks in advance. We are just looking at this for internal reporting.

Leave a Reply

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