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

ii. Navigate to Top Menu “Data” then “Get Data” then “Launch Power Query Editor”

Image showing how to navigate to "Launch Power Query Editor"

iii. On the “Power Query Window”, add a new Blank Query Source (Home à New Source à Other Sources à Blank Query)

Image showing how to navigate to "New Query"

iv. Click on the newly created source “Query1” on the left window pane under “Queries”

Image showing how to navigate to open Query1

v. Click on the “Advanced Editor” under the “Home” ribbon menu (This will open a Query popup Window)

Image showing how to navigate to "Advanced Editor"

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, 
							[Implementation="2.0, "Headers = [Authorization = bearerValue]])
in
	apiResponse

// –> example

CBR_1

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

Image showing error to ignore and "Continue" button

ix. On the “Privacy levels” window, check on “Ignore Privacy Levels checks for this file….”

Image showing the "Privacy Levels" warning

x. Click “Save” (This will automatically pull the data from the API source into the Power Query Window)

Image showing the data in the "Power Query" window

xi. Click on “Close & Load” button under “Home” ribbon menu (“Home” à “Close & Load”)

Image showing how to Navigate to "Close and Load"

xii. The data will now be loaded into the excel worksheet

Image showing an Excel spreadsheet with the data loaded