CloudExtend Excel Analytics for NetSuite does an amazing job of getting your NetSuite saved searches into Excel eliminating the need to copy/paste CSV exports. Users can build out dashboards and other models in Excel, and, with the push of a button, or even on a hands-off schedule, CloudExtend will deliver the most recent results from the saved search to Excel.
Learn why we also recommend integrating Excel Power Query into your workflows
We recommend using CloudExtend to deliver your NetSuite data to an Excel workbook which will then become your source data, accessed via Power Query, either from Excel or Power BI.
Here are some of the reasons to consider Power Query.
Power Query will protect the integrity of your master data sheet
Power Query will not overwrite formulas in your Excel file
If you're using CloudExtend to run your saved searches on a schedule it will minimize issues with other users editing the worksheet while it's being updated
Power Query can be used to transform your data if desired
Power Query can be used to relate unlimited NetSuite saved searches together by common key fields
Power Query can consolidate multiple files in a single SharePoint folder into one data model
Power Query allows users to go well beyond the 1 million row limit of Excel
Power Query is ridiculously fast
Now that we've said that it's worth noting that you don't have to use Power Query to be successful with CloudExtend. If you're building models for yourself and aren't using Excel formulas in the tables created by CloudExtend you'll probably just use one Excel workbook as your source and then analyze or dashboard your data in other worksheets in the same workbook.
Let's build our first model with CloudExtend and Excel Power Query
Watch the accompanying video to see it in action.
The first step is to create our source workbook.
Create a new Excel file and save it on OneDrive for Business or SharePoint (we recommend incorporating Source Data into the name).
Open the CloudExtend Excel Analytics for NetSuite add-in and select your saved search. Optionally, put it on a schedule. If you're new to CloudExtend learn how here.
Next, we'll need to get the location of this file for our next step. From Excel, choose File> Info> Copy Path
That's it!
You have now successfully created your source data file. If you've put this on a schedule (recommended) you won't need to touch it again. If you have not, then you will still need to do a manual refresh with CloudExtend (but hey, it sure beats copy/paste from CSV files).
Close your source data file
The next step is to create your analysis workbook.
This is the fun part. We're going to show you how to connect to your source data. You'll be amazed at all Power Query can do.
Create a new Excel file and save it, preferably on OneDrive for Business or SharePoint
Select Get Data> From Other Source> From Web (you might be tempted to choose 'from file', don't).
Next, paste the data you copied from File > Info > Get Path in the previous section. This is the link to your Excel File on SharePoint. For some weird reason, you also need to remove the
?web=1from the URL (let's just call it a Microsoft quirk).
You will then be presented with a list of table names in your Excel source workbook as well as worksheet names. If it's your first time to log in, you will be prompted to enter your Microsoft credentials. Follow the instructions here.
Once logged in the list of names will show. Choose the table name, not the worksheet name. Doing so will automatically promote your headers and save you a step later. Last, click Load to load the most recent set of data from your source workbook.
💡 Sidenote:Your table names are derived from the name of your NetSuite saved search.
Now you're ready to analyze your data.
Logging in to Microsoft 365
If it's your first time to log in, you will be prompted to enter your Microsoft credentials.
Step 1: Choose Organization account > Sign in and enter your credentials.
Step 2: Hit Connect.
Bonus Tips
🔵 Put your data queries on a schedule
You can always click Data >Refresh in the ribbon to manually refresh your data from the source file. You can also fully automate your dashboard and reporting from end to end by scheduling your NetSuite saved search and then adding a schedule to the query in your analysis file that's connected to the source file. To put your query on a schedule, select Data >Queries & Connections and then right-click on the query and select Properties. Here you'll be able to select how often you want your query to refresh from the source data file.
🔵 Add persistent formulas to your table
If you add formulas to a table that is updated by CloudExtend, ie your original search results, that formula is overwritten whenever you refresh the search with CloudExtend. Instead, add your formulas to the analysis file connected to the source file via Power Query and they will remain intact during a refresh. Pretty handy!
🔵 Cleanse your source file with Power Query
Not every source file has perfect data. Excel Power Query can help clean up your data every time it runs. In the example below we're removing a column from the source data and also changing the data type for a date field that was not recognized properly.
🔵 Connect multiple NetSuite saved searches together
We've just uncovered the beginning when it comes to Power Query. Here's a link to another article we wrote that shows you to add your searches to a data model and relate them by common key fields. Now you can relate an unlimited number of NetSuite saved searches together.