Power BI Query Reference

Querying Multiple Organizations

To query a single MaintainX organization, you can use the queries in this article as-is. To query multiple organizations in the same Power BI data source, you have to modify the header line to specify which organizations you want to query.

In the query examples in this article, the header line is line 5:

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

To query multiple organizations, change the header line to:

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

tip

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

Assets

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/assets/assets.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Name", type text},
      {"ID", Int64.Type},
      {"Parent", type text},
      {"Location", type text},
      {"Location ID", Int64.Type},
      {"Parent Location", type text},
      {"Parent ID", Int64.Type},
      {"Types", type text},
      {"URL", type text},
      {"Description", type text},
      {"Barcode", type text},
      {"Teams in Charge", type text},
      {"Vendors", type text},
      {"Vendor IDs", type text},
      {"Created by", type text},
      {"Created on", type datetime},
      {"Last updated", type datetime},
      {"Thumbnail", type text}
    }
  )
in
  #"Changed Type"

Asset Status

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/assetstatuses/assetstatus.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"ID", Int64.Type},
      {"Status", type text},
      {"Custom Status", type text},
      {"Custom Status ID", Int64.Type},
      {"Downtime Type", type text},
      {"Started At", type datetime},
      {"Ended At", type datetime},
      {"Duration", type text},
      {"Note", type text},
      {"Created By", type text},
      {"Created On", type datetime},
      {"Asset ID", Int64.Type},
      {"Asset Name", type text}
    }
  ),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Ended At", DateTime.LocalNow()}})
in
    #"Replaced Errors"

Set a date range for this query

By default, this query returns one month of data. You can retrieve a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/assetstatuses/assetstatus.csv"

With:

"https://api.getmaintainx.com/v1/assetstatuses/assetstatus.csv?fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Locations

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/locations/locations.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Name", type text},
      {"ID", Int64.Type},
      {"Parent", type text},
      {"Parent ID", Int64.Type},
      {"Address", type text},
      {"URL", type text},
      {"Description", type text},
      {"Teams in Charge", type text},
      {"Vendors", type text},
      {"Vendor IDs", type text},
      {"Created by", type text},
      {"Created on", type datetime},
      {"Last updated", type datetime},
      {"Thumbnail", type text}
    }
  )
in
  #"Changed Type"

Meters

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/meters/meters.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Name", type text},
      {"ID", Int64.Type},
      {"Unit Of Measure (U/M)", type text},
      {"Measurement Type", type text},
      {"Reading Frequency", type text},
      {"Asset", type text},
      {"Asset ID", Int64.Type},
      {"Location", type text},
      {"Location ID", Int64.Type},
      {"Last Reading", type number},
      {"Last Read on", type datetime},
      {"Next Reading on", type datetime},
      {"Created by", type text},
      {"Created on", type datetime},
      {"Last updated", type datetime},
      {"Thumbnail", type text}
    }
  )
in
  #"Changed Type"

Meter Readings

Manual

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/meterReadings/meterReadings.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Meter Name", type text},
      {"Meter ID", Int64.Type},
      {"Unit Of Measure (U/M)", type text},
      {"Reading", type number},
      {"Entered on", type datetime},
      {"Entered by", type text}
    }
  )
in
  #"Changed Type"

Set a date range for this query

By default, this query returns one month of data. You can set a date range to retrieve more data.

For best results, we recommend a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/meterReadings/meterReadings.csv"

With:

"https://api.getmaintainx.com/v1/meterReadings/meterReadings.csv?fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Internet of Things (IoT)

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/meterReadings/meterReadings.csv?measurementType=IOT_DEVICE",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Meter Name", type text},
      {"Meter ID", Int64.Type},
      {"Unit Of Measure (U/M)", type text},
      {"Count", type number},
      {"Minimum", type number},
      {"Average", type number},
      {"Median", type number},              
      {"Since", type datetime},
      {"Until", type datetime}
    }
  )
in
  #"Changed Type"

Set a date range for this query

By default, this query returns one month of data. You can set a date range to retrieve more data.

For best results, we recommend a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/meterReadings/meterReadings.csv?measurementType=IOT_DEVICE"

With:

"https://api.getmaintainx.com/v1/meterReadings/meterReadings.csv?measurementType=IOT_DEVICE&fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Parts

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/parts/parts.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"ID", Int64.Type},
      {"Area", type text},
      {"Assets", type text},
      {"Asset IDs", type text},
      {"QR/Bar code", type text},
      {"Description", type text},
      {"Location", type text},
      {"Location ID", Int64.Type},
      {"Minimum Quantity", Int64.Type},
      {"Name", type text},
      {"Part Numbers", type text},
      {"Quantity in Stock", Int64.Type},
      {"Teams in Charge", type text},
      {"Thumbnail", type text},
      {"Types", type text},
      {"Unit Cost", type number},
      {"URL", type text},
      {"Vendors", type text},
      {"Vendor IDs", type text},
      {"Created by", type text},
      {"Created on", type datetime},
      {"Last updated", type datetime}
    }
  )
in
  #"Changed Type"

Part Transactions

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/parts/transactions/transactions.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Transaction ID", Int64.Type},
      {"Direction", type text},
      {"Part ID", Int64.Type},
      {"Part Name", type text},
      {"QR/Bar code", type text},
      {"Part Location", type text},
      {"Quantity Before", Int64.Type},
      {"Quantity Added to Inventory", Int64.Type},
      {"Quantity After", Int64.Type},
      {"Unit Cost", Int64.Type},
      {"Total Cost", Int64.Type},
      {"Transaction Date", type datetime},
      {"Work Order ID", Int64.Type},
      {"Work Order URL", type text},
      {"Work Order Asset", type text},
      {"Work Order Asset ID", Int64.Type},
      {"Work Order Location", type text},
      {"Work Order Location ID", Int64.Type},
      {"Transaction Initiator", type text},
      {"Transaction Type", type text},
      {"Transaction Reason", type text},
      {"Part Area", type text},
      {"Part Types", type text}
    }
  )
in
  #"Changed Type"

Set a date range for this query

By default, this query returns one month of data. You can set a date range to retrieve more data.

For best results, we recommend a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/parts/transactions/transactions.csv"

With:

"https://api.getmaintainx.com/v1/parts/transactions/transactions.csv?fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Procedure Fields

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/workorders/procedure/fields.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Work Order ID", Int64.Type},
      {"Procedure Template ID", Int64.Type},
      {"Procedure Name", type text},
      {"Procedure Template Field ID", Int64.Type},
      {"Field ID", Int64.Type},
      {"Field Name", type text},
      {"Field Type", type text},
      {"Field Value", type text},
      {"Filled At", type datetime},
      {"Filled By", type text},
      {"Filled By User ID", Int64.Type},
      {"Field Notes", type text},
      {"Meter", type text},
      {"Meter ID", Int64.Type},
      {"Attachments", type text}
    }
  )
in
  #"Changed Type"

Set a date range for this query

By default, this query returns one month of data. You can set a date range to retrieve more data.

For best results, we recommend a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/workorders/procedure/fields.csv"

With:

"https://api.getmaintainx.com/v1/workorders/procedure/fields.csv?fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Purchase Orders

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/purchaseorders/purchaseorders.csv",
      [Headers=[Authorization="Bearer " & Token]]
    ),
    [Delimiter=",",  Encoding=65001, QuoteStyle=QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Purchase Order #", type text},
      {"Purchase Order ID", Int64.Type},
      {"Purchase Order Title", type text},
      {"Created On", type datetime},
      {"Completed On", type datetime},
      {"Due Date", type datetime},
      {"Vendor", type text},
      {"Billing Address", type text},
      {"Shipping Address", type text},
      {"Status", type text},
      {"Notes", type text},
      {"Line Number", Int64.Type},
      {"Line Type", type text},
      {"Line Name", type text},
      {"Part Id", Int64.Type},
      {"Ordered Quantity", Int64.Type},
      {"Received Quantity", Int64.Type},
      {"Ordered Cost", Int64.Type},
      {"Received Cost", Int64.Type},
      {"Total Ordered Cost", Int64.Type},
      {"Total Received Cost", Int64.Type}
    }
  )
  in
    #"Changed Type"

Set a date range for this query

By default, this query returns one month of data. You can set a date range to retrieve more data.

For best results, we recommend a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/purchaseorders/purchaseorders.csv"

With:

"https://api.getmaintainx.com/v1/purchaseorders/purchaseorders.csv?fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Time and Cost

Grouped by User

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/workorders/costs/workorderscosts.csv",
      [Headers=[Authorization="Bearer " & Token]]
    ),
    [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"User ID", Int64.Type},
      {"User Name", type text},
      {"Open Work Order Time (hours)", type text},
      {"Open Work Order Time Costs", Currency.Type},
      {"Open Work Order Other Costs", Currency.Type},
      {"Open Work Order Total Costs", Currency.Type},
      {"Closed Work Order Time (hours)", type text},
      {"Closed Work Order Time Costs", Currency.Type},
      {"Closed Work Order Other Costs", Currency.Type},
      {"Closed Work Order Total Costs", Currency.Type},
      {"Time (hours)", type text},
      {"Total Costs", Currency.Type}
    }
  ),
  #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Open Work Order Time (hours)", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"OWOT.1", "OWOT.2", "OWOT.3"}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"OWOT.1", Int64.Type}, {"OWOT.2", Int64.Type}, {"OWOT.3", Int64.Type}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Closed Work Order Time (hours)", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"CWOT.1", "CWOT.2", "CWOT.3"}),
  #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"CWOT.1", Int64.Type}, {"CWOT.2", Int64.Type}, {"CWOT.3", Int64.Type}}),
  #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Time (hours)", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time.1", "Time.2", "Time.3"}),
  #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Time.1", Int64.Type}, {"Time.2", Int64.Type}, {"Time.3", Int64.Type}}),
  #"Added Custom" = Table.AddColumn(#"Changed Type3", "Open Work Order Time (Hours)", each [#"OWOT.1"]+([#"OWOT.2"]/60)+([#"OWOT.3"]/3600)),
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "Closed Work Order Time (Hours)", each [#"CWOT.1"]+([#"CWOT.2"]/60)+([#"CWOT.3"]/3600)),
  #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Total Time (Hours)", each [#"Time.1"]+([#"Time.2"]/60)+([#"Time.3"]/3600)),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"OWOT.1", "OWOT.2", "OWOT.3", "CWOT.1", "CWOT.2", "CWOT.3", "Time.1", "Time.2", "Time.3"})
in
  #"Removed Columns"

Set a date range for this query

By default, this query returns one month of data. You can set a date range to retrieve more data.

For best results, we recommend a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/workorders/costs/workorderscosts.csv"

With:

"https://api.getmaintainx.com/v1/workorders/costs/workorderscosts.csv?fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Individual Entries

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/workorders/costs/entries/workorderscostsentries.csv",
      [Headers=[Authorization="Bearer " & Token]]
    ),
    [Delimiter=",",  Encoding=65001, QuoteStyle=QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Work Order Title", type text},
      {"Work Order ID", Int64.Type},
      {"User", type text},
      {"Creator", type text},
      {"Created On", type datetime},
      {"Type", type text},
      {"Cost", Currency.Type},
      {"Cost Type", type text},
      {"Time Type", type text},
      {"Description", type text},
      {"Time (hours)", type text},
      {"Hourly Rate", Currency.Type}
    }
	),
  #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Time (hours)", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time (hours).1", "Time (hours).2", "Time (hours).3"}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"User ID", Int64.Type}, {"Creator ID", Int64.Type}, {"Time (hours).1", Int64.Type}, {"Time (hours).2", Int64.Type}, {"Time (hours).3", Int64.Type}}),
  #"Added Custom" = Table.AddColumn(#"Changed Type1", "Time (Hours)", each [#"Time (hours).1"]+([#"Time (hours).2"]/60)+([#"Time (hours).3"]/3600)),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Time (hours).1", "Time (hours).2", "Time (hours).3"}),
  #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Work Order Title", "Work Order ID", "User", "User ID", "Creator", "Creator ID", "Created On", "Type", "Cost", "Cost Type", "Time Type", "Description", "Time (Hours)", "Hourly Rate"})
in
	#"Reordered Columns"

Set a date range for this query

By default, this query returns one month of data. You can set a date range to retrieve more data.

For best results, we recommend a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/workorders/costs/entries/workorderscostsentries.csv"

With:

"https://api.getmaintainx.com/v1/workorders/costs/entries/workorderscostsentries.csv?fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Users

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/users/users.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"ID", Int64.Type},
      {"User", type text},
      {"Role", type text},
      {"Email", type text},
      {"Phone Number", Int64.Type},
      {"Teams", type text},
      {"WO Created", Int64.Type}
    }
  )
in
  #"Changed Type"

Vendors

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/vendors/vendors.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"ID", Int64.Type},
      {"Vendor", type text},
      {"Description", type text},
      {"Contact Name", type text},
      {"Role", type text},
      {"Email", type text},
      {"Phone Number", type text},
      {"Phone Ext.", Int64.Type},
      {"Assets", type text},
      {"Parts", type text},
      {"Locations", type text}
    }
  )
in
  #"Changed Type"

Work Orders

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"

Set a date range for this query

By default, this query returns one month of data. You can set a date range to retrieve more data.

For best results, we recommend a maximum of three months of data at once. If you need more than 3 months of data, you can create multiple queries and append them together.

To gather 3 months of data:

Replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/workorders/workorders.csv"

With:

"https://api.getmaintainx.com/v1/workorders/workorders.csv?fromUTC=YYYY-MM-DD&toUTC=YYYY-MM-DD"

Include Planned Work Orders

To include planned work orders in your report, replace the following line (highlighted in the main query above):

"https://api.getmaintainx.com/v1/workorders/workorders.csv",

With:

"https://api.getmaintainx.com/v1/workorders/workorders.csv?planned=true",

Work Requests

let
  Source = Csv.Document(
    Web.Contents(
      "https://api.getmaintainx.com/v1/workRequests/workRequests.csv",
      [Headers = [Authorization = "Bearer " & Token]]
    ),
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"ID", Int64.Type},
      {"Title", type text},
      {"Status", type text},
      {"Priority", type text},
      {"Description", type text},
      {"Location Name", type text},
      {"Location ID", Int64.Type},
      {"Creation Date", type datetime},
      {"Creator", type text},
      {"Work Order", type text},
      {"Work Order ID", Int64.Type},
      {"Asset", type text},
      {"Asset ID", Int64.Type}
    }
  )
in
  #"Changed Type"

Querying Multiple Organizations​

Assets​

Asset Status​

Locations​

Meters​

Meter Readings​

Manual​

Internet of Things (IoT)​

Parts​

Part Transactions​

Procedure Fields​

Purchase Orders​

Time and Cost​

Grouped by User​

Individual Entries​

Users​

Vendors​

Work Orders​

Include Planned Work Orders​

Work Requests​

Querying Multiple Organizations

Assets

Asset Status

Locations

Meters

Meter Readings

Manual

Internet of Things (IoT)

Parts

Part Transactions

Procedure Fields

Purchase Orders

Time and Cost

Grouped by User

Individual Entries

Users

Vendors

Work Orders

Include Planned Work Orders

Work Requests