First, you’ll want to enable the web query functionality on a given report. You’ll do this as shown below:
Goto desired Report>Click on Customize
Once the Customize screen loads, navigate to the More Options button as depicted below:
After you’ve navigated to the More Options tab, check the box allowing Web Query access:
Now that you’ve checked the box, rename and make sure to save the report:
When you click the save button, NetSuite will render the new report. If you scroll down to the bottom of the page, you’ll notice a new icon. Click on this icon, illustrated below:
Selecting this icon should generate a .iqy file:
Clicking on this link directly will open up Excel where we will be prompted for credentials (email). However, what we want to do is prepare this source so that we can effectively connect NetSuite to Power BI. To do this, we will need to save the file in a location so that we can open it with text editor. Right click to do so:
Once the file location opens, right click the .iqy file to utilize the “Open With” command, and use a qualified text editor.
The file exported by NetSuite contains a URL. Deleted the unwanted part from the URL and make it as follows:
Replace the text XXXXXX in the above URL with just your email address in the format: name@netsuitelogindomain.com.
To test to see if this is successful, copy and paste the text in a browser. It should return a table rendered in HTML format. Save the URL.
Now, we are ready to connect NetSuite to Power BI and view this report.
Next, open Power BI Desktop and click on the “Get Data” icon:
Type in “Web” and select the appropriate Web source to import data from a web page:
When prompted, enter the URL saved earlier into the dialog box, then select “OK”:
You’ll be presented with the data navigator where you should select “Table 0”:
Selecting this table should render a data preview to the right of your selection
Ensure that the data rendered matches the data you want to import into Power BI Desktop. If so, We connected NetSuite to Power BI.
Now, click the Edit button to make a few quick transformations to the data. The Query Editor in Power BI Desktop, shown below, allows you perform several data preparation tasks without code.
