From a Secured Endpoint
(note: This can only be done once confirmed that client ID provided to the xMart administrator has already been granted the rights to use the specific Mart tables data)
i. Launch excel spreadsheet
ii. Navigate to Top Menu “Data” à “Get Data” à “Launch Power Query Editor”
iii. On the “Power Query Window”, add a new Blank Query Source (Home à New Source à Other Sources à Blank Query)
iv. Click on the newly created source “Query1” on the left window pane under “Queries”
v. Click on the “Advanced Editor” under the “Home” ribbon menu (This will open a Query popup Window)
vi. Put this set of script in the newly opened window and replace the parameters needed (ClientID, Client Secret, and xMart API link to pull the data from)
// –> script
let
client_id = "<Your ClientID Here>", // --> your client id
client_secret = "<Your Client Secret Here>", // --> your secret
token_uri = "https://login.microsoftonline.com/f610c0b7-bd24-4b39-810b-3dc280afb590/oauth2/token",
xMartApiUatId = "b85362d6-c259-490b-bd51-c0a730011bef",
xMartApiProdId = "712b0d0d-f9c5-4b7a-80d6-8a83ee014bca",
tokenResponse = Web.Contents
(
token_uri, [Content=Text.ToBinary(Uri.BuildQueryString
(
[
client_id = client_id,
client_secret = client_secret,
grant_type = "client_credentials",
``
// --> change the resource value below to UAT or PROD if needed, also change the apiResponse url below accordingly
// -----> xMartApiUatId => For UAT (Default)
// -----> xMartApiProdId => For PROD
``
resource = xMartApiUatId
]
)
), Headers=[#"Content-type"="application/x-www-form-urlencoded", Accept="application/json"]]),
jwtBody = Json.Document(tokenResponse),
jwtResult = if (Record.HasFields(jwtBody, {"error", "error_description"}))
then
error Error.Record(jwtBody[error], jwtBody[error_description], jwtBody)
else
jwtBody,
access_token = jwtResult[access_token],
bearerValue = "Bearer " & access_token,
apiResponse = OData.Feed("<Your API Enpoint Here>", null, [Headers = [Authorization = bearerValue]])
in
apiResponse
// –> example
vii. Press “Done” (This will return you to the Power Query Window)
viii. You will notice a Yellow background warning sign, Click on “Continue” button
ix. On the “Privacy levels” window, check on “Ignore Privacy Levels checks for this file….”
x. Click “Save” (This will automatically pull the data from the API source into the Power Query Window)
xi. Click on “Close & Load” button under “Home” ribbon menu (“Home” à “Close & Load”)
xii. The data will now be loaded into the excel worksheet
