Skip to main content

Power BI — Containers

Pulls all containers with their current location, type, and a list of the IDs of assets, inventory, and packages they hold.


How to Set Up​

  1. Follow the setup steps to configure privacy settings
  2. In Power BI Desktop, open Transform data → Transform data
  3. Click New Source → Blank Query, then open Advanced Editor
  4. Paste the query below
  5. In the code, replace your@email.com and YourPassword123! at the top with your Xemelgo credentials
  6. Click Done and rename the query (e.g., Xemelgo Containers)

M Query​

let
Email = Binary.ToText(Text.ToBinary("your@email.com", TextEncoding.Utf8), BinaryEncoding.Base64),
Password = Binary.ToText(Text.ToBinary("YourPassword123!", TextEncoding.Utf8), BinaryEncoding.Base64),

Token = Json.Document(
Web.Contents("https://rest.api.xemelgo.com/login", [
Headers = [#"Content-Type" = "application/json"],
Content = Json.FromValue([email = Email, password = Password])
])
)[IdToken],

FetchPage = (nextToken as nullable text) =>
let
Resp = Json.Document(
Web.Contents("https://api.xemelgo.com/graphql", [
Headers = [Authorization = Token, #"Content-Type" = "application/json"],
Content = Json.FromValue([
query = "query($n: String) {
containers(input: { nextToken: $n }) {
containers {
id name description trackerSerial comments
creationDate lastUpdatedDate
assetIds inventoryIds packageIds
childContainerIds parentContainerId
customProperties
type { id name }
location { id name }
lastDetectedAtLocation { id name }
}
nextToken
}
}",
variables = [n = nextToken]
])
])
)[data][containers]
in [items = Resp[containers], nextToken = Resp[nextToken]],

Pages = List.Generate(
() => FetchPage(null),
each _ <> null,
each if _[nextToken] = null then null else FetchPage(_[nextToken]),
each _[items]
),
All = List.Combine(Pages),
Tbl = Table.FromList(All, Splitter.SplitByNothing()),
Expanded = Table.ExpandRecordColumn(Tbl, "Column1",
{"id","name","description","trackerSerial","comments",
"creationDate","lastUpdatedDate",
"assetIds","inventoryIds","packageIds",
"childContainerIds","parentContainerId","customProperties",
"type","location","lastDetectedAtLocation"},
{"ID","Name","Description","Tracker Serial","Comments",
"Created","Last Updated",
"Asset IDs","Inventory IDs","Package IDs",
"Child Container IDs","Parent Container ID","Custom Properties",
"type","location","lastDetectedAtLocation"}),
WithType = Table.ExpandRecordColumn(Expanded,
"type", {"id","name"}, {"Container Type ID","Container Type Name"}),
WithLocation = Table.ExpandRecordColumn(WithType,
"location", {"id","name"}, {"Location ID","Location Name"}),
WithLastDetected = Table.ExpandRecordColumn(WithLocation,
"lastDetectedAtLocation", {"id","name"}, {"Last Detected Location ID","Last Detected Location Name"}),
WithDates = Table.TransformColumns(WithLastDetected, {
{"Created", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"Last Updated", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime}
})
in
WithDates

Columns Returned​

ColumnDescription
IDUnique container identifier
NameContainer name
Tracker SerialRFID tag serial
Created / Last UpdatedTimestamps
Asset IDsList of asset IDs inside this container
Inventory IDsList of inventory IDs inside this container
Package IDsList of package IDs inside this container
Child Container IDsNested child containers
Parent Container IDParent container if nested
Container Type ID / NameContainer type
Location ID / NameAssigned location
Last Detected Location ID / NameLocation of last RFID read

Tip: To expand contents into individual rows (one row per asset), add Table.ExpandListColumn(WithDates, "Asset IDs") as an additional step.