Use Excel Power Query and CloudExtend to analyze your NetSuite data

XLANS | Learn why CloudExtend recommends connecting to your data source with Excel Power Query

Updated over a week ago

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.

  1. Power Query will protect the integrity of your master data sheet

  2. Power Query will not overwrite formulas in your Excel file

  3. 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

  4. Power Query can be used to transform your data if desired

  5. Power Query can be used to relate unlimited NetSuite saved searches together by common key fields

  6. Power Query can consolidate multiple files in a single SharePoint folder into one data model

  7. Power Query allows users to go well beyond the 1 million row limit of Excel

  8. 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=1 from 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.



Did this answer your question?