Analytics - Direct Query Links in Microsoft Excel

Direct Query Web Links can be used to create PowerBI data sources which pull data from NetSuite directly.

The web link can be copied from the Data Source record in NetSuite. If the Data Source does not show a Direct Query Link URL make sure that you have updated the SuiteBundle to the latest version and that you have followed the instructions in the Direct Query Web Links article.

If you have not yet generated the Token and Secret needed to authenticate, you can follow the instructions in the article Generating Tokens & Secrets for Direct Queries.

Use in Microsoft Excel Query Data

Microsoft Excel allows adding live data queries to workbooks to dynamically refresh data from an external source. The procedure for adding a NetSuite data source using Direct Query links is a little different depending on if one is using Microsoft Excel for Windows, for Mac or Excel Online.

Either way, the first step is to generate the direct query link as above. It is highly recommended to use the CSV format when building links to embed into Microsoft Excel as the process for JSON data is more complicated and requires using the "To Table" option.

Microsoft Excel for Windows

Under the Data tab in Excel, use the menu option to Get Data > From Other Sources > From Web.

When prompted, add the link to the URL field. Note that this URL does not contain the Token & Secret.

If you have not used links before, you will be prompted to setup authentication. Select "Basic" from the menu on the left and then enter the Token under User name and the Token Secret under Password and then click Connect.

If successful Excel will display a page where you can transform and format the data source. You may need to refer to Excel documentation for details on what all the options do on this page.

Excel will next give you a data preview and prompt you to Load or Transform Data. If Excel shows an error message in the preview such as "invalid login" try to click the refresh data icon in the top right (blue arrow). Once the data looks correct, click Load or for more advanced options such as renaming headers or pivoting or filtering data, click the Transform Data button.

If you do select the option to Transform Data the screen will look like this. To complete the optation select Close & Load in the top left under the Home tab.

If importing data in JSON format you may need to work with the "To Table" option or alternatively use the Advanced Editor to edit the query the the code console. Some simple examples of how to do this are provided in the PowerQuery section below.

How to open the PowerQuery advanced editor:

You will now have a live data feed you can refresh and work with in Excel.

Microsoft Excel for Mac

The procedure for setting up queries in Microsoft Excel for Mac is similar but there is no "From Web" option available at the time of writing.

In Excel, Navigate in the menus under Data > Get Data > Get Data (Power Query)

Here you will be prompted with a list of options similar to this. Find and select the Blank Query option.

From here you can follow the remainder of the instructions below for adding data sources with PowerQuery.

Adding Data Sources with PowerQuery

The PowerQuery editor can be used for more advanced queries in Excel and for Mac it is required to add any queries from web data sources.

The PowerQuery editor is a code editor for Microsoft's query language. The below is what it looks like before any edits are made.

For more advanced queries you can find data on Microsoft's site. But here are some very simple examples for CSV and JSON data to just load a single link to Excel. Replace the URL with your full Direct Query Link from SuperSync Analytics.

CSV Data Format

let     Source = Csv.Document(Web.Contents("https://ssal.supersync.cloud?format=csv&data=my-data-source")) in     Source

JSON Data Format

Here is a simple example on how to parse JSON data using the PowerQuery editor. Column names are entered (green).

let     Source = Table.FromRecords(         Json.Document(Web.Contents("https://ssal.supersync.cloud?format=json&data=my-data-source"))      , {"internalid", "externalid", "customer_name"} ) in     Source

When complete you can click Next on the advanced editor to preview the result.

If you have not yet added the credentials into Excel you will see an error that credentials are missing or invalid. Click on the Configure connection button to add credentials.

Choose Basic authentication and enter the Token in the Username field and the Secret in the Password field. Generally you will only need to do this the first time and Excel will thereafter store the credentials. Click Connect when done.

This will bring you back to the transform screen where you can further edit the results, add field data types or do more advanced transformations like pivots.

After previewing and editing the results you can Close & Load with the button in the top left.

Resetting Store Credentials

If you ever need to reset stored credentials in PowerBI, you can do so under the Get Data > Data Source Settings... menu item.

Was this article helpful?