Skip to main content

Connect Microsoft Power BI to MaintainX

After you create a Reporting API key in MaintainX, you can set up a data source in Power BI, export your MaintainX data, and view it in Power BI.

Create a Parameter for Your MaintainX API Key​

A parameter in Power BI is a variable that you can use in queries. To connect to MaintainX you have to set up a parameter for your Reporting API key.

To create a parameter in Power BI:

  1. Open Power BI Desktop.

  2. Select the Home tab in the ribbon, and then select Get Data > Blank Query.

  3. In the Power Query Editor, select Manage Parameters > New Parameter.

  4. Configure the new parameter as follows:

    FieldValue
    NameToken
    Current ValuePaste in the MaintainX PowerBI API key you generated earlier (see Generate a MaintainX API key for PowerBI).

  5. Select OK.

  6. In the Power Query Editor, select your new query.

  7. In the Query Settings, change the Name to something more meaningful than the default name.

    For example, if you're creating a query for your work orders data, you can name it WorkOrders.

Create a Data Source​

After you set up your parameter in Power BI, you can create a data source to specify what MaintainX data you want to export into Power BI. This section uses the work orders data source as an example, but you can find a complete list of possible data sources in the Power BI Query Reference.

To create a data source in Power BI:

  1. In Power BI Desktop, select the query you created earlier.

  2. Select the View tab on the ribbon, and then select Advanced Editor.

  3. Delete all of the default content in the Advanced Editor.

  4. Paste the query for your data source into the Advanced Editor.

    For example, if you want to use work orders as a data source, paste in the following query:

    Example: Work Orders Query
    let
      Source = Csv.Document(
        Web.Contents(
          "https://api.getmaintainx.com/v1/workorders/workorders.csv",
          [Headers = [Authorization = "Bearer " & Token]]

        ),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
      ),
      #"WorkOrders Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
      #"WorkOrders Change Type" = Table.TransformColumnTypes(
        #"WorkOrders Promoted Headers",
        {
          {"ID", Int64.Type},
          {"Title", type text},
          {"Status", type text},
          {"Priority", type text},
          {"Description", type text},
          {"URL", type text},
          {"Global ID", Int64.Type},
          {"Assigned to", type text},
          {"Teams Assigned to", type text},
          {"Requested by", type text},
          {"Requested on", type datetime},
          {"Created by", type text},
          {"Created on", type datetime},
          {"Planned Start Date", type datetime},
          {"Due date", type datetime},
          {"Started on", type datetime},
          {"Completed on", type datetime},
          {"Completed by", type text},
          {"Last updated", type datetime},
          {"Recurrence", type text},
          {"Location", type text},
          {"Location ID", Int64.Type},
          {"Asset", type text},
          {"Asset ID", Int64.Type},
          {"Categories", type text},
          {"Vendors", type text},
          {"Vendor IDs", type text},
          {"Procedure Flags", Int64.Type},
          {"Procedure Failures", Int64.Type},
          {"Time to Complete", type text},
          {"Time Open", type text},
          {"Time In Progress", type text},
          {"Time On Hold", type text}
        }
      ),
        #"Split Column by Delimiter" = Table.SplitColumn(#"WorkOrders Change Type", "Total Reported Time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"TRT.1", "TRT.2", "TRT.3"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Procedure Name", type text}, {"Procedure Template ID", Int64.Type}, {"Procedure Passes", Int64.Type}, {"Procedure Score (%)", Int64.Type}, {"Total Parts Cost", Currency.Type}, {"Total Time Cost", Currency.Type}, {"TRT.1", Int64.Type}, {"TRT.2", Int64.Type}, {"TRT.3", Int64.Type}, {"Additional Costs", Currency.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Total Reported Time (Hours)", each [TRT.1]+([TRT.2]/60)+([TRT.3]/3600)),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Added Custom", "Time to Complete", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"TTC.1", "TTC.2", "TTC.3"}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Time Open", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"TO.1", "TO.2", "TO.3"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Time In Progress", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"TIP.1", "TIP.2", "TIP.3"}),
        #"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3", "Time On Hold", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"TOH.1", "TOH.2", "TOH.3"}),
        #"Split Column by Delimiter5" = Table.SplitColumn(#"Split Column by Delimiter4", "Estimated Time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"ET.1", "ET.2", "ET.3"}),
        #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"ET.1", Int64.Type}, {"ET.2", Int64.Type}, {"ET.3", Int64.Type}, {"TOH.1", Int64.Type}, {"TOH.2", Int64.Type}, {"TOH.3", Int64.Type}, {"TIP.1", Int64.Type}, {"TIP.2", Int64.Type}, {"TIP.3", Int64.Type}, {"TO.1", Int64.Type}, {"TO.2", Int64.Type}, {"TO.3", Int64.Type}, {"TTC.1", Int64.Type}, {"TTC.2", Int64.Type}, {"TTC.3", Int64.Type}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type4", "Time to Complete (Hours)", each [TTC.1]+([TTC.2]/60)+([TTC.3]/3600)),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Time Open (Hours)", each [TO.1]+([TO.2]/60)+([TO.3]/3600)),
        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Time in Progress (Hours)", each [TIP.1]+([TIP.2]/60)+([TIP.3]/3600)),
        #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Time on Hold (Hours)", each [TOH.1]+([TOH.2]/60)+([TOH.3]/3600)),
        #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Estimated Time (Hours)", each [ET.1]+([ET.2]/60)+([ET.3]/3600)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"TTC.1", "TTC.2", "TTC.3", "TO.1", "TO.2", "TO.3", "TIP.1", "TIP.2", "TIP.3", "TOH.1", "TOH.2", "TOH.3", "TRT.1", "TRT.2", "TRT.3", "ET.1", "ET.2", "ET.3"})
    in
        #"Removed Columns"

    For a complete list of queries for available MaintainX data sources, see the Power BI Query Reference.

  5. Make sure that the authentication header in the query contains a reference to the data source and a reference to the parameter that you created for your API key.

    For example, in the header of the work orders query:

    • On line 4, https://api.getmaintainx.com/v1/workorders/workorders.csv is the reference to the data source in MaintainX.
    • On line 5, Token is the reference to the parameter. If you named your parameter something else, replace Token with the name you chose.
       let
      Source = Csv.Document(
        Web.Contents(
          "https://api.getmaintainx.com/v1/workorders/workorders.csv",
          [Headers = [Authorization = "Bearer " & Token]]
        ),

  6. In the Advanced Editor, select Done.

Multi-Organization Reporting​

If you’re creating a data source from multiple MaintainX organizations, you have to edit the query header.

For example, in the header of the work orders query, change line 5 from:

[Headers = [Authorization = "Bearer " & Token]]

to:

[Headers = [Authorization = "Bearer " & Token,#"x-organization-ids"="**OrgID1,OrgID2,...**"]]

If you don’t know your MaintainX Org IDs, your MaintainX representative can provide them.

Edit Your Credentials and Connect to MaintainX​

After you set up your data source in Power BI, you have to connect to MaintainX to bring your data into Power BI.

To connect Power BI to MaintainX:

  1. In Power BI Desktop, select your query.
  2. Select Edit Credentials. The Access Web content dialog opens.
  3. Select Anonymous, and then select Connect.

The results of your query appear in the Power Query Editor.

View Your MaintainX Data in the Power BI Dashboard​

After you bring your MaintainX data into Power BI, you can use Power BI's dashboard visualizations to create reports. For help creating specific reports and visualizations, contact your MaintainX representative.

You can publish your Power BI reports to share them with stakeholders. For more information, see Publish a Power BI Report.