#Using excel in office 365 drivers#
#Using excel in office 365 driver#

ODBC Drivers for REST API, JSON, XML, SOAP, OData.

In SQL statement section enter this query (use the NOTE ID you retrieved in the previous step):.

Create a new data source based on System – XML DSN (we are using XML-based DSN because the response will be in HTML):.

Once you get a result set, find your note name and copy its ID:.

Then go to the Preview tab, and input this SQL query and hit Preview Data:.

To do that, open ODBC Administrator, find System – JSON data source and hit Configure.

First, we will need to find NOTE ID we are trying to get.

Suppose, you have these notes in OneNote and you want “ Groceries list” to be shown in a Power BI report:

Drag a table control into the report, select several columns and you should see a similar view:.

You can try calling other Calendar endpoints. Note: Use Microsoft Graph API Explorer to make a call and see what response you get.

You can read more about transformations in Parse multi-dimensional JSON array article. To accomplish that, we override ArrayTransformType. With ArrayTransColumnNameFilter property and text, we specify that the first array of parent array will be the headers of the columns. You can configure this option in the Filter Options tab ( read more about JSON path).We also have to make the transformation on the JSON and turn JSON arrays into rows. We use in $.values to skip the first row because the first row contains the headers and we want only the data. The Filter property enables us to select the data we want to see in the report.

In this step we are overriding Url, Filter and array transformation fields you configured in JSON DSN. You can generate the query above using the Query Builder: Using WITH clause lets us override the properties. ,ArrayTransformType='TransformSimpleTwoDimensionalArray' WITH(Src=' YOUR FOLDER/Stock.xlsx:/workbook/worksheets/ SHEET NAME/usedRange' Then in SQL statement area enter this query:.Create a data source based on System – JSON DSN (since we get a JSON response):.Open Power BI Desktop and create a new Power BI report.To have that data in a Power BI report, perform these steps: Let’s say you have Stock.xlsx stored in Sample Documents folder, OneDrive with this kind of data and you want to show it in a Power BI report:

#Using excel in office 365 driver#

Paste the copied connection string and replace JSON text to XML, so that you have in DRIVER property.Ĭreate a Power BI report Reading files from OneDrive (getting Excel data).Then press Load connection string button to load the connection string.Once a window is opened, give it a name, e.g.Close the window and then add a new data source, but this time based on ZappySys XML Driver.Before adding another data source based on ZappySys XML Driver, copy the connection string of the data source we just created:.Step By Step – Get Office 365 data in Power BI using Microsoft Graph API and ODBC ODBC PowerPack allows to query REST/SOAP APIs in an easy way and lets to view responses in a tabular format, which will be perfect to accomplish our goal. We will achieve that by creating an ODBC data source using ZappySys ODBC PowerPack and then querying corresponding Microsoft Graph API methods. Specifically, we will get Excel file data in OneDrive, events from a Calendar and finally, a list in a note in OneNote and then load that data into a Power BI report. In this article, we will get Office 365 data in Power BI using Microsoft Graph API and ODBC drivers. 6 Manage gateways and configure the schedule.5 Working with Gateways in Power BI (Schedule Import).4.4.1 Reading files from OneDrive (getting Excel data).4.3 Set up an ODBC data source based on ZappySys XML Driver.4.2 Set up an ODBC data source based on ZappySys JSON Driver.4.1 Open ODBC Data Source Administrator.4 Register Application (OAuth2 App for Graph API).3 Step By Step – Get Office 365 data in Power BI using Microsoft Graph API and ODBC.