Power BI — Transfer Orders
Three queries are available — one for each transfer order type: Asset, Inventory, and Package.
Note: The Transfer Order API retrieves orders by ID. You need to know the transfer order ID(s) you want to pull. See the multi-order pattern below if you have a list of IDs.
How to Set Up
- Follow the setup steps to configure privacy settings
- In Power BI Desktop, open Transform data → Transform data
- Click New Source → Blank Query, then open Advanced Editor
- Paste one of the queries below
- In the code, replace
your@email.com,YourPassword123!, andOrderIdat the top with your values - Click Done and rename the query (e.g.,
Xemelgo Asset Transfer Order)
Query 1 — Asset Transfer Order
let
Email = Binary.ToText(Text.ToBinary("your@email.com", TextEncoding.Utf8), BinaryEncoding.Base64),
Password = Binary.ToText(Text.ToBinary("YourPassword123!", TextEncoding.Utf8), BinaryEncoding.Base64),
OrderId = "YOUR_TRANSFER_ORDER_ID",
Token = Json.Document(
Web.Contents("https://rest.api.xemelgo.com/login", [
Headers = [#"Content-Type" = "application/json"],
Content = Json.FromValue([email = Email, password = Password])
])
)[IdToken],
Resp = Json.Document(
Web.Contents("https://api.xemelgo.com/graphql", [
Headers = [Authorization = Token, #"Content-Type" = "application/json"],
Content = Json.FromValue([
query = "query($id: String!) {
assetTransferOrder(input: { id: $id }) {
id identifier state
creationDate lastUpdatedDate
expectedShipDate expectedReceiveDate actualShipDate actualReceiveDate
senderLocation { id name }
receiverLocation { id name }
assets { id name trackerSerial }
customProperties
}
}",
variables = [id = OrderId]
])
])
)[data][assetTransferOrder],
Tbl = Table.FromRecords({Resp}),
WithSender = Table.ExpandRecordColumn(Tbl, "senderLocation", {"id","name"}, {"Sender Location ID","Sender Location Name"}),
WithReceiver = Table.ExpandRecordColumn(WithSender, "receiverLocation", {"id","name"}, {"Receiver Location ID","Receiver Location Name"}),
WithDates = Table.TransformColumns(WithReceiver, {
{"creationDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"lastUpdatedDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"expectedShipDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"expectedReceiveDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"actualShipDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"actualReceiveDate", 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: id, identifier, state, creationDate, lastUpdatedDate, expectedShipDate, expectedReceiveDate, actualShipDate, actualReceiveDate, Sender Location, Receiver Location, assets (list), customProperties
Query 2 — Inventory Transfer Order
let
Email = Binary.ToText(Text.ToBinary("your@email.com", TextEncoding.Utf8), BinaryEncoding.Base64),
Password = Binary.ToText(Text.ToBinary("YourPassword123!", TextEncoding.Utf8), BinaryEncoding.Base64),
OrderId = "YOUR_TRANSFER_ORDER_ID",
Token = Json.Document(
Web.Contents("https://rest.api.xemelgo.com/login", [
Headers = [#"Content-Type" = "application/json"],
Content = Json.FromValue([email = Email, password = Password])
])
)[IdToken],
Resp = Json.Document(
Web.Contents("https://api.xemelgo.com/graphql", [
Headers = [Authorization = Token, #"Content-Type" = "application/json"],
Content = Json.FromValue([
query = "query($id: String!) {
inventoryTransferOrder(input: { id: $id }) {
id identifier state
creationDate lastUpdatedDate
expectedShipDate expectedReceiveDate actualShipDate actualReceiveDate
senderLocation { id name }
receiverLocation { id name }
inventory { id name trackerSerial }
customProperties
}
}",
variables = [id = OrderId]
])
])
)[data][inventoryTransferOrder],
Tbl = Table.FromRecords({Resp}),
WithSender = Table.ExpandRecordColumn(Tbl, "senderLocation", {"id","name"}, {"Sender Location ID","Sender Location Name"}),
WithReceiver = Table.ExpandRecordColumn(WithSender, "receiverLocation", {"id","name"}, {"Receiver Location ID","Receiver Location Name"}),
WithDates = Table.TransformColumns(WithReceiver, {
{"creationDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"lastUpdatedDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"expectedShipDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"expectedReceiveDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"actualShipDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"actualReceiveDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime}
})
in
WithDates
Query 3 — Package Transfer Order
let
Email = Binary.ToText(Text.ToBinary("your@email.com", TextEncoding.Utf8), BinaryEncoding.Base64),
Password = Binary.ToText(Text.ToBinary("YourPassword123!", TextEncoding.Utf8), BinaryEncoding.Base64),
OrderId = "YOUR_TRANSFER_ORDER_ID",
Token = Json.Document(
Web.Contents("https://rest.api.xemelgo.com/login", [
Headers = [#"Content-Type" = "application/json"],
Content = Json.FromValue([email = Email, password = Password])
])
)[IdToken],
Resp = Json.Document(
Web.Contents("https://api.xemelgo.com/graphql", [
Headers = [Authorization = Token, #"Content-Type" = "application/json"],
Content = Json.FromValue([
query = "query($id: String!) {
packageTransferOrder(input: { id: $id }) {
id identifier state
creationDate lastUpdatedDate
expectedShipDate expectedReceiveDate actualShipDate actualReceiveDate
senderLocation { id name }
receiverLocation { id name }
packages { id name trackerSerial }
customProperties
}
}",
variables = [id = OrderId]
])
])
)[data][packageTransferOrder],
Tbl = Table.FromRecords({Resp}),
WithSender = Table.ExpandRecordColumn(Tbl, "senderLocation", {"id","name"}, {"Sender Location ID","Sender Location Name"}),
WithReceiver = Table.ExpandRecordColumn(WithSender, "receiverLocation", {"id","name"}, {"Receiver Location ID","Receiver Location Name"}),
WithDates = Table.TransformColumns(WithReceiver, {
{"creationDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"lastUpdatedDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"expectedShipDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"expectedReceiveDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"actualShipDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime},
{"actualReceiveDate", each if _ = null then null else #datetime(1970,1,1,0,0,0) + #duration(0,0,0,_/1000), type nullable datetime}
})
in
WithDates
Pulling Multiple Orders at Once
If you have a list of IDs, replace the single-order query with this pattern:
let
Email = Binary.ToText(Text.ToBinary("your@email.com", TextEncoding.Utf8), BinaryEncoding.Base64),
Password = Binary.ToText(Text.ToBinary("YourPassword123!", TextEncoding.Utf8), BinaryEncoding.Base64),
OrderIds = {"ORDER-001", "ORDER-002", "ORDER-003"},
Token = Json.Document(
Web.Contents("https://rest.api.xemelgo.com/login", [
Headers = [#"Content-Type" = "application/json"],
Content = Json.FromValue([email = Email, password = Password])
])
)[IdToken],
FetchOrder = (id as text) =>
Json.Document(
Web.Contents("https://api.xemelgo.com/graphql", [
Headers = [Authorization = Token, #"Content-Type" = "application/json"],
Content = Json.FromValue([
query = "query($id: String!) { assetTransferOrder(input: { id: $id }) { id identifier state senderLocation { id name } receiverLocation { id name } } }",
variables = [id = id]
])
])
)[data][assetTransferOrder],
Results = List.Transform(OrderIds, FetchOrder),
Tbl = Table.FromList(Results, Splitter.SplitByNothing()),
Expanded = Table.ExpandRecordColumn(Tbl, "Column1",
{"id","identifier","state","senderLocation","receiverLocation"},
{"ID","Identifier","State","senderLocation","receiverLocation"}),
WithSender = Table.ExpandRecordColumn(Expanded, "senderLocation", {"id","name"}, {"Sender Location ID","Sender Location Name"}),
WithReceiver = Table.ExpandRecordColumn(WithSender, "receiverLocation", {"id","name"}, {"Receiver Location ID","Receiver Location Name"})
in
WithReceiver