Getting started with MachineMetrics POST APIs using PowerBI

If you're using PowerBI and are interested in getting data directly from your MachineMetrics dashboard, this article will help you get started! For further documentation on our APIs, see our Developer page.

Note: We don't provide technical support for PowerBI, but we're happy to answer any questions you might have about our APIs. 

In this Article, we will be using PowerBI Desktop

1. Open PowerBI. In this example, we're using a blank project, but you could add our data to an existing project as well

2. From the home menu bar, click Get Data

3. Select Blank Query from the list of options

4. Next, click on your new Query from the left side Queries bar, then click the Advanced Editor option

5. This is where we'll be entering all the details of your API request. We recommend copying and pasting the example POST request below to serve as a starting point. Just remember to delete the original contents of your Query

let
 APIKey = "bearer ",
 APIToken = "YourAPIKeyHere",
 url = "https://api.machinemetrics.com/reports/production", 
 startDate = DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-30),"yyyy-MM-ddTHH:mm:ssZ"),
 endDate = DateTime.ToText(DateTime.LocalNow(),"yyyy-MM-ddTHH:mm:ssZ"),
 body = Json.FromValue([start = startDate, 
   end = endDate, 
     data={ 
       [metric = "totalParts"], 
       [metric = "rejectedParts"], 
       [metric = "goodParts"], 
       [metric = "setupTime"],
       [metric = "timeInCut"],
       [metric = "timeInCycle"]
 },
     groupBy = {
       [group = "day"],
       [group = "shift"],
       [group = "machine"]
 },
     flatten = true
 ]), 
 JSONRetrive =
   Web.Contents(url, 
     [
     Headers = 
       [
       #"Authorization" = APIKey & APIToken,
       #"Content-Type" = "application/json"
       ],
     Content = body
     ]
   ),
JSONText = Text.FromBinary(JSONRetrive), 
JSONResult = Json.Document(JSONRetrive), 
 items = JSONResult[items],
   #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error), //Converts the item list to a table
   #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"machine", "day", "totalParts", "setupTime", "timeInCycle"}, {"Column1.machine", "Column1.day", "Column1.totalParts", "Column1.setupTime", "Column1.timeInCycle"}), 
   #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.day", "Day"}, {"Column1.machine", "Machine"}, {"Column1.setupTime", "Setup Time"}, {"Column1.timeInCycle", "Time In-Cycle"}, {"Column1.totalParts", "Total Parts"}}) 
in
   #"Renamed Columns"

Note: Dates must be in valid ISO 8601 date-time format (e.g., "2017-07-19T10:00:00Z"). Additionally, you may need to adjust for your local timezone. Below is a 5 hour offset example.

endDate = DateTime.ToText(DateTime.LocalNow()+ #duration(0,5,0,0),"yyyy-MM-ddTHH:mm:ssZ")

6. Before you finish, you'll need to enter your API key into the designated area on line 3. If you're not sure where to get your MachineMetrics API key, just take a look at this Knowledge Base article.

If you want to change the date range of your request, you'll need to edit what is being assigned to startDate and endDate. If you want to add more body parameters, you can follow the syntax shown in the example above, or use JSON format with the following exceptions. M code (what Power Query uses) has a few important differences that need to be observed.

If you'd like to learn more about Power Query or M Code, Microsoft has excellent documentation which can be found here

Note: While you don't use quotes around the key in each key-value pair, you still use quotes around the value.

Once you've made all the desired changes, click Done

7. If you see a "Please Specify how to connect" banner appear. Click the Edit Credentials button

8. Make sure Anonymous is selected from the left side menu, then select your API endpoint from the dropdown. In this example, we're using "https://api.machinemetrics.com/reports/production". Once you have it selected, click Connect

9. It may take a moment for your query to finish loading, but once it does, you should see a table similar to the one in the image below. To start visualizing your data, click the Close & Apply button

Note: If you're including a date or time in your query, such as hour or day, PowerBI may not recognize the data as a date. To resolve this, edit the column expand the "Change Type" menu, then select "Date/Time/Timezone"

10. You should now have a functional table of data to work with! If you're looking to get started learning PowerBI reporting, you can find tutorials here

Multiple Locations

If you have multiple locations and would like to aggregate the data, this sample code can help get you started!

let
APIKeys = {"API Key Here", "API Key Here"}, // Array of API keys
url = "https://api.machinemetrics.com/reports/production",
startDate = DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-30),"yyyy-MM-ddTHH:mm:ssZ"),
endDate = DateTime.ToText(DateTime.LocalNow(),"yyyy-MM-ddTHH:mm:ssZ"),
body = Json.FromValue([start = startDate,
end = endDate,
data = {
[metric = "totalParts"],
[metric = "utilizationRate"]
},
groupBy = {
[group = "day"],
[group = "machine"]
},
flatten = true
]),
CombineTables = (tables) => // Function to combine multiple tables into a single table
let
combined = Table.Combine(tables),
renumbered = Table.AddIndexColumn(combined, "Index"),
sorted = Table.Sort(renumbered, {{"Index", Order.Ascending}}),
removed = Table.RemoveColumns(sorted, {"Index"})
in
removed,
GetTableForAPIKey = (APIKey) => // Function to get a table for a single API key
let
APIKeyHeader = [
#"Authorization" = "bearer " & APIKey,
#"Content-Type" = "application/json"
],
JSONRetrive = Web.Contents(url, 
[
Headers = APIKeyHeader,
Content = body
]
),
JSONResult = Json.Document(JSONRetrive),
items = JSONResult[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"machine", "day", "totalParts", "setupTime", "timeInCycle"}, {"Machine", "Day", "Total Parts", "Setup Time", "Time In-Cycle"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Day", "Day"}, {"Machine", "Machine"}, {"Setup Time", "Setup Time"}, {"Time In-Cycle", "Time In-Cycle"}, {"Total Parts", "Total Parts"}})
in
#"Renamed Columns",
Tables = List.Transform(APIKeys, each GetTableForAPIKey(_)), // Get a table for each API key
CombinedTable = CombineTables(Tables) // Combine all tables into a single table
in
CombinedTable

Troubleshooting

If you are attempting to load a Desktop Power BI query to the web version of Power BI, you may encounter the following error during the testing process.

If you do, please check the Skip test connection box and try again.

If you have any questions, please contact your customer success manager or support@machinemetrics.com!